select distinct - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

select distinct

Author

Topic

ianott
Starting Member

6 Posts

Posted - 2014-11-14 : 12:48:37

I am new to SQL and am trying to use the Select Distinct statement and am having some issues getting it to work. When I use it on only 1 column it is working fine, but I want to use it on 2 columns it does not do what I expect it to. Can someone help me out, here is my code:


SELECT *
INTO #NEWTEMP
FROM DBO.REPORTDATA;

ALTER TABLE #NEWTEMP
ALTER COLUMN UTCTime DATE 

Select Distinct UTCTime, SignalID, Count(1) as SignalIDCount
INTO #SignalIDCount
FROM #NEWTEMP (NOLOCK)
Group by UTCTime, SignalID
Order by UTCTime, SignalID


Select 
A.SignalID, A.fValue,A.UTCTime, B.SignalIDCount
FROM
(
Select 
SignalID, 
UTCTime,
fValue,
ROW_NUMBER() OVER(PARTITION BY SignalID ORDER BY fValue DESC) AS 'Sub_Rank'
FROM #NEWTEMP (NOLOCK) ) A
INNER JOIN #SignalIDCount B ON
A.SignalID = B.SignalID
Where A.Sub_rank = 1
Group by A.SignalID, A.fValue,A.UTCTime, A.Sub_Rank, B.SignalIDCount

RESULTS

In the results table I have 3 entries for each signalid with the same UTCTime, I only want to have 1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-14 : 12:54:11

Which one do you want?

Here it is with MAX:

Select
A.SignalID, A.fValue,A.UTCTime, MAX(B.SignalIDCount) AS SignalIDCount
FROM
(
Select
SignalID,
UTCTime,
fValue,
ROW_NUMBER() OVER(PARTITION BY SignalID ORDER BY fValue DESC) AS 'Sub_Rank'
FROM #NEWTEMP (NOLOCK) ) A
INNER JOIN #SignalIDCount B ON
A.SignalID = B.SignalID
Where A.Sub_rank = 1
Group by A.SignalID, A.fValue,A.UTCTime, A.Sub_Rank

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ianott
Starting Member

6 Posts

Posted - 2014-11-14 : 13:18:21

Thanks for responding, but I do not think that will help me. The problem is that only 1 of those results is correct. For SignalID 1100 the result with a SignalIDCount of 40 is correct, but for 1100 the one with SignalIDCount of 1 is correct. Somehow I am making extras show up in my results.

What I want is to:
1 - Take the data from dbo.ReportData, copy it into #NEWTEMP
2 - Change the UTCTime column from datatype DATETIME to datatype DATE
3 - Count how many times each SignalId was recorded each day
4 - Take the maximum value for fValue for each signal each day

Does that make sense?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-14 : 13:21:14

You'll need to post sample data at this point (before and after).

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-14 : 13:21:42

Here's an article that shows how to post it: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ianott
Starting Member

6 Posts

Posted - 2014-11-14 : 14:20:36

FIRST TABLE

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable

--===== Create the test table with
CREATE TABLE #mytable
(
[SignalID] [float] NULL,
[UTCTime] [datetime] NULL,
[fValue] [float] NULL,

)

SELECT 'SELECT '
+ QUOTENAME(SignalID,'''')+','
+ QUOTENAME(UTCTime,'''')+','
+ QUOTENAME(fValue,'''')+','
+ ' UNION ALL'
FROM ReportData

--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable ON

--===== Insert the test data into the test table
INSERT INTO #mytable
(SignalID, UTCTime, fValue)

SELECT '1100','Oct 1 2014 10:30PM','1', UNION ALL
SELECT '1100','Oct 1 2014 10:30PM','5', UNION ALL
SELECT '1101','Oct 1 2014 10:30PM','6', UNION ALL
SELECT '1100','Oct 1 2014 10:30PM','14', UNION ALL
SELECT '1101','Oct 1 2014 10:30PM','13', UNION ALL
SELECT '1102','Oct 1 2014 10:30PM','12', UNION ALL
SELECT '1100','Oct 1 2014 10:30PM','10', UNION ALL
SELECT '1101','Oct 1 2014 10:30PM','9', UNION ALL
SELECT '1102','Oct 1 2014 10:30PM','8', UNION ALL
SELECT '1103','Oct 1 2014 10:30PM','7', UNION ALL
SELECT '1100','Oct 2 2014 10:30PM','11', UNION ALL
SELECT '1101','Oct 2 2014 10:30PM','21', UNION ALL
SELECT '1102','Oct 2 2014 10:30PM','31', UNION ALL
SELECT '1103','Oct 2 2014 10:30PM','41', UNION ALL
SELECT '1101','Oct 2 2014 10:30PM','61', UNION ALL
SELECT '1102','Oct 2 2014 10:30PM','161', UNION ALL
SELECT '1103','Oct 2 2014 10:30PM','151', UNION ALL
SELECT '1102','Oct 2 2014 10:30PM','121', UNION ALL
SELECT '1103','Oct 2 2014 10:30PM','111', UNION ALL
SELECT '1103','Oct 2 2014 10:30PM','71'

--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable ON

DESIRED RESULTING TABLE

--===== Create the test table with
CREATE TABLE #mytable2
(
[SignalID] [float] NULL,
[UTCTime] [datetime] NULL,
[fValue] [float] NULL,

)

SELECT 'SELECT '
+ QUOTENAME(SignalID,'''')+','
+ QUOTENAME(UTCTime,'''')+','
+ QUOTENAME(fValue,'''')+','
+ QUOTENAME(SignalIDCount,'''')+','
+ ' UNION ALL'
FROM #mytable

--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable2 ON

--===== Insert the test data into the test table
INSERT INTO #mytable2
(SignalID, UTCTime, fValue, SignalIDCount)

SELECT '1100','Oct 1 2014 10:30PM','14','4', UNION ALL
SELECT '1101','Oct 1 2014 10:30PM','13','3', UNION ALL
SELECT '1102','Oct 1 2014 10:30PM','12','2', UNION ALL
SELECT '1103','Oct 1 2014 10:30PM','7','1', UNION ALL
SELECT '1100','Oct 2 2014 10:30PM','11','1', UNION ALL
SELECT '1101','Oct 2 2014 10:30PM','61','2', UNION ALL
SELECT '1102','Oct 2 2014 10:30PM','151','3', UNION ALL
SELECT '1103','Oct 2 2014 10:30PM','151''4'

--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable2 ON

ianott
Starting Member

6 Posts

Posted - 2014-11-14 : 15:38:34

I figured it out.... I had to change my join statement in the last block of code....

Select
A.SignalID, A.fValue, B.UTCTime, B.SignalIDCount
FROM
(
Select SignalID, UTCTime, fValue,
ROW_NUMBER() OVER(PARTITION BY SignalID, UTCTime ORDER BY fValue DESC) AS 'Sub_Rank'
FROM #NEWTEMP (NOLOCK) ) A
right JOIN #SignalIDCount B ON
A.SignalID = B.SignalID AND A.UTCTime = B.UTCTime
Where A.Sub_rank = 1
Group by A.SignalID, A.fValue, B.UTCTime, A.Sub_Rank, B.SignalIDCount

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources