SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 ROW_NUMBER DISTINCT QUERY
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Johnph
Yak Posting Veteran

95 Posts

Posted - 02/28/2013 :  13:28:13  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3557 Posts

Posted - 02/28/2013 :  13:36:11  Show Profile  Reply with Quote
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
Yak Posting Veteran

95 Posts

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

James K
Flowing Fount of Yak Knowledge

3557 Posts

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

Johnph
Yak Posting Veteran

95 Posts

Posted - 02/28/2013 :  14:13:44  Show Profile  Reply with Quote
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.

Edited by - Johnph on 02/28/2013 14:18:32
Go to Top of Page

Johnph
Yak Posting Veteran

95 Posts

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

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1642 Posts

Posted - 02/28/2013 :  14:35:39  Show Profile  Reply with Quote
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


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

James K
Flowing Fount of Yak Knowledge

3557 Posts

Posted - 02/28/2013 :  14:36:18  Show Profile  Reply with Quote
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
Yak Posting Veteran

95 Posts

Posted - 02/28/2013 :  14:51:08  Show Profile  Reply with Quote
trying this now!
Go to Top of Page

Johnph
Yak Posting Veteran

95 Posts

Posted - 02/28/2013 :  15:31:29  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000