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/
Go to Top of Page

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?
Go to Top of Page

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/
Go to Top of Page

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/
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -