Just want one result and getting 8 - Subquery? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2005 Forums

Transact-SQL (2005)

Just want one result and getting 8 - Subquery?

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-08-07 : 15:19:52

[code]
SELECT STATION_ID, OPERATOR_ID, MAX(END_DATE_TIME) MAX_END_DATE_TIME, SYNCH_TYPE
FROM SYS_SYNCH_LOG
WHERE STATION_ID = 13
GROUP BY STATION_ID, OPERATOR_ID, SYNCH_TYPE
[/code]I am geting:[code]
STATION_ID OPERATOR_ID MAX_END_DATE_TIME SYNCH_TYPE
13 34 12/7/2007 3:59:40 PM A
13 139 12/4/2007 8:37:25 AM A
13 466 8/7/2009 9:01:05 AM A
13 139 9/6/2007 9:56:29 AM D
13 466 6/20/2008 8:57:40 AM D
13 139 9/21/2007 3:05:58 PM Q
13 466 8/6/2009 5:14:44 PM Q
13 466 7/22/2009 2:25:03 PM T[/code]
I really want only the 3rd row but I need to display all the columns. I think a sub-query is in order but don't know how to write it. As a bonus, if someone knows a JOIN clause to use it in, it would even be better, but I at least want to get the query to work, with 1 result. Thank you.

Duane

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-08-07 : 15:25:48

It's because there are numerous differanct Operator_ID's and Sync_Type's you will need to filter more.


SELECT STATION_ID, Max(OPERATOR_ID) as OOperator_ID, MAX(END_DATE_TIME) MAX_END_DATE_TIME, max(Synch_Type) as Synch_Type
FROM SYS_SYNCH_LOG
WHERE STATION_ID = 13
GROUP BY STATION_ID

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-08-07 : 16:17:13

I very much appreciate the answer and it worked, but I was working and researching while waiting for an answer. If I am correct, filtering on muliple columns seems like it would be more inefficient as I would be having to further filter results after retrieving them. I am wondering if the following idea would be more efficient and correct. If you see any pitfalls here, let me know, but I think this focuses on the correct result from the start using a sub-query in the WHERE clause. Let me know what you think. Here it is:

SELECT S.STATION_ID, S.OPERATOR_ID, S.END_DATE_TIME, S.SYNCH_TYPE
FROM SYS_SYNCH_LOG S
WHERE S.END_DATE_TIME =
(SELECT MAX(END_DATE_TIME)
FROM SYS_SYNCH_LOG
WHERE STATION_ID = S.STATION_ID)
AND S.STATION_ID=13

Duane

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-08 : 07:38:00

try like this


SELECT S.STATION_ID, S.OPERATOR_ID, S.END_DATE_TIME, S.SYNCH_TYPE
FROM SYS_SYNCH_LOG S 
inner join 
(SELECT STATION_ID,MAX(END_DATE_TIME)as END_DATE_TIME
FROM SYS_SYNCH_LOG group by STATION_ID) ss on s.STATION_ID = ss.STATION_ID and s.END_DATE_TIME = ss.END_DATE_TIME
where S.STATION_ID=13

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-08 : 07:55:17

[code]SELECT STATION_ID,
OPERATOR_ID,
END_DATE_TIME,
SYNCH_TYPE
FROM (
SELECT STATION_ID,
OPERATOR_ID,
END_DATE_TIME,
SYNCH_TYPE,
ROW_NUMBER() OVER (PARTITION BY STATION_ID ORDER BY END_DATE_TIME DESC) AS recID
FROM SYS_SYNCH_LOG
) AS d
WHERE recID = 1[/code]

N 56°04'39.26"
E 12°55'05.63"

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-08-14 : 12:02:22

Interesting results here. When I took out Station_ID=13 (let all station_IDs get retrieved) and put an ORDER BY Station_ID in at the end of each, of all 4 queries here (including my own followup but not the original post), the last 3 had identical results. The first of the 4 had the same number of records, the same Station_IDs, and the same End_Date_Time, but believe it or not had different Operator_IDs and Synch_Types even though the 2 other columns were identical to all the other queries. I don't know if there are identical End_Date_Times for these Operator_IDs or not. I just wanted to present these observations tho help someon (even myself) to understand. Thank you all for your input.

Duane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources