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) aORDER BY TIME DESC This will give these results:Cat Time Valid Indicator1 4:00 INVALID2 4:00 INVALID <I dont want this row>2 4:00 VALID3 5:00 INVALID4 6:00 INVALID <I dont want this row>4 6:00 VALID5 4:00 INVALID6 5:00 INVALID6 5:40 INVALIDBasically, 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 Indicator1 4:00 INVALID2 4:00 VALID3 5:00 INVALID4 6:00 VALID5 4:00 INVALID6 5:00 INVALID6 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 functionSELECT 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) sGROUP BY [TIME],[Valid Indicator] |
|
|
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. |
|
|
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....) sGROUP BY [TIME]; |
|
|
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. |
|
|
Johnph
Posting Yak Master
103 Posts |
Posted - 2013-02-28 : 14:27:49
|
Maybe I need some type of PARTITION clause any ideas? |
|
|
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) aGROUP BY a.CAT, a.TIMEORDER BY TIME DESC[/CODE]=================================================There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber |
|
|
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 ) bDROP TABLE #table1, #table2; |
|
|
Johnph
Posting Yak Master
103 Posts |
Posted - 2013-02-28 : 14:51:08
|
trying this now! |
|
|
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) aGROUP BY a.CAT, a.TIMEORDER BY TIME DESCEnded up working but thanks james + busta for the help. you guys rock |
|
|
|