| Author |
 Topic  |
|
|
Johnph
Yak Posting Veteran
76 Posts |
 Posted - 02/28/2013 : 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
Flowing Fount of Yak Knowledge
1743 Posts |
Posted - 02/28/2013 : 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
) s
GROUP BY
[TIME],[Valid Indicator]
|
 |
|
|
Johnph
Yak Posting Veteran
76 Posts |
Posted - 02/28/2013 : 13:46:57
|
| hmm, that doesn't seem to work. gives me the same results. |
|
|
|
James K
Flowing Fount of Yak Knowledge
1743 Posts |
Posted - 02/28/2013 : 13:47:38
|
Sorry, you don't need the group by on valid indicator. Change to....
) s
GROUP BY
[TIME]; |
|
|
|
Johnph
Yak Posting Veteran
76 Posts |
Posted - 02/28/2013 : 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.
|
Edited by - Johnph on 02/28/2013 14:18:32 |
|
|
|
Johnph
Yak Posting Veteran
76 Posts |
Posted - 02/28/2013 : 14:27:49
|
| Maybe I need some type of PARTITION clause any ideas? |
|
|
|
Bustaz Kool
Flowing Fount of Yak Knowledge
USA
1440 Posts |
Posted - 02/28/2013 : 14:35:39
|
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 |
|
|
|
James K
Flowing Fount of Yak Knowledge
1743 Posts |
Posted - 02/28/2013 : 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; |
|
|
|
Johnph
Yak Posting Veteran
76 Posts |
Posted - 02/28/2013 : 14:51:08
|
| trying this now! |
|
|
|
Johnph
Yak Posting Veteran
76 Posts |
Posted - 02/28/2013 : 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
|
|
|
| |
Topic |
|
ROW_NUMBER DISTINCT QUERY
