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
 ROW_NUMBER DISTINCT QUERY

Author  Topic 

Johnph
Posting Yak Master

103 Posts

Posted - 2013-02-28 : 13:28:13
I am in need of some help with a query. I believe that I am supposed to use ROW_NUMBER syntax but I am not 100% sure. Below is the query and the output and also the output that I want.

SELECT  a.CAT, a.TIME, a.[Valid Indicator] from 
(SELECT CAT, TIME, 'VALID' as [Valid Indicator] FROM TABLE1 UNION
SELECT CAT, TIME, 'INVALID' as [Valid Indicator] FROM TABLE2) a
ORDER BY TIME DESC


This will give these results:

Cat Time Valid Indicator
1 4:00 INVALID
2 4:00 INVALID <I dont want this row>
2 4:00 VALID
3 5:00 INVALID
4 6:00 INVALID <I dont want this row>
4 6:00 VALID
5 4:00 INVALID
6 5:00 INVALID
6 5:40 INVALID


Basically, when CAT and TIME are the same, I want it to retain the last value that was entered instead.

The results that I want is this:

Cat Time Valid Indicator
1 4:00 INVALID
2 4:00 VALID
3 5:00 INVALID
4 6:00 VALID
5 4:00 INVALID
6 5:00 INVALID
6 5:40 INVALID

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-28 : 13:36:11
If the time and Cat are the same, what can be used to determine which of the two rows in your example was entered last? Setting that issue aside, you can try something like this to use row_number function
SELECT 
ROW_NUMBER() OVER (ORDER BY [TIME]) AS Cat,
[TIME],[Valid Indicator]
FROM
(
SELECT [TIME],'VALID' as [Valid Indicator] FROM TABLE1
UNION ALL
SELECT [TIME],'INVALID' as [Valid Indicator] FROM TABLE2
) s
GROUP BY
[TIME],[Valid Indicator]
Go to Top of Page

Johnph
Posting Yak Master

103 Posts

Posted - 2013-02-28 : 13:46:57
hmm, that doesn't seem to work. gives me the same results.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-28 : 13:47:38
Sorry, you don't need the group by on valid indicator. Change to
....
) s
GROUP BY
[TIME];
Go to Top of Page

Johnph
Posting Yak Master

103 Posts

Posted - 2013-02-28 : 14:13:44
Getting this error:

Column 'CAT' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I dont think I can group by one column.
Go to Top of Page

Johnph
Posting Yak Master

103 Posts

Posted - 2013-02-28 : 14:27:49
Maybe I need some type of PARTITION clause any ideas?
Go to Top of Page

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-02-28 : 14:35:39
[CODE]SELECT a.CAT, a.TIME, MAX(a.[Valid Indicator]) from
(SELECT CAT, TIME, 'VALID' as [Valid Indicator] FROM TABLE1 UNION
SELECT CAT, TIME, 'INVALID' as [Valid Indicator] FROM TABLE2) a
GROUP BY a.CAT, a.TIME
ORDER BY TIME DESC[/CODE]

=================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-28 : 14:36:18
The perils of writing code without sample data to test! I created some test data - see below. If this does not do what you want, can you change the input data (the insert statements) to include sample data that causes incorrect results and post back?
CREATE TABLE #Table1(Cat int, time TIME);
CREATE TABLE #Table2(Cat int, time TIME);
INSERT INTO #Table1 VALUES (2,'4:00');
INSERT INTO #Table2 VALUES (1,'4:00'),(2,'4:00')



;WITH cte AS
(
SELECT Cat,[TIME],'VALID' as [Valid Indicator] FROM #TABLE1
UNION ALL
SELECT Cat,[TIME],'INVALID' as [Valid Indicator] FROM #TABLE2
)
SELECT
a.Cat,
a.Time,
b.[Valid Indicator]
FROM
(SELECT DISTINCT Cat,Time FROM cte) a
CROSS APPLY
(
SELECT TOP (1) [Valid Indicator]
FROM cte b
WHERE b.Time = a.Time AND b.Cat = a.Cat
ORDER BY [Valid Indicator] DESC
) b

DROP TABLE #table1, #table2;
Go to Top of Page

Johnph
Posting Yak Master

103 Posts

Posted - 2013-02-28 : 14:51:08
trying this now!
Go to Top of Page

Johnph
Posting Yak Master

103 Posts

Posted - 2013-02-28 : 15:31:29
Thank you both for the help.

SELECT a.CAT, a.TIME, MAX(a.[Valid Indicator]) from
(SELECT CAT, TIME, 'VALID' as [Valid Indicator] FROM TABLE1 UNION
SELECT CAT, TIME, 'INVALID' as [Valid Indicator] FROM TABLE2) a
GROUP BY a.CAT, a.TIME
ORDER BY TIME DESC

Ended up working but thanks james + busta for the help. you guys rock

Go to Top of Page
   

- Advertisement -