January 31, 2009 at 12:12 pm
What you be the best syntax?
I need to get from a table, from a particular set of rows, the one with the max date
Syntax 1:
SELECT TOP 1 * FROM MyTable
WHERE MyForeignIdColumn = 400
ORDER BY MyDateColumn DESC
Syntax 2:
SELECT * FROM MyTable
WHERE MyDateColumn = (SELECT max(MyDateColumn) from MyTable WHERE MyForeignIdColumn = 400)
thank you
Martin
January 31, 2009 at 5:50 pm
It depends on whether or not you believe in the myth of portable code. TOP is an SQL Server manifestation.
Other than that, "best", in my book, is the one that does all of the following...
1. Works correctly
2. Is the fastest
3. Uses the least amount of resources (memory, drive, cpu... in that order for me).
Now, you're asking a question as to which is best. Lemme ask you a question... who are you going to believe? Answer should be, "No One". "A Developer must not guess... a Developer must KNOW." Set up a test and discover the correct answer.
For the record, I don't believe in the myth. 😉
--Jeff Moden
February 2, 2009 at 6:58 am
I agree with Jeff, portable code is a myth.
For the type of query you're running, let me say one thing, look at the execution plan for each query. I wouldn't be at all surprised if you saw the same operation occurring for both queries. In this instance, that'll give you your answer.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 2, 2009 at 7:36 am
If there are multiple rows with the same date-time in the MyDateColumn, the first query will only give you one of them, but the second query will give you all of them. That needs to be taken into account when you decide which is "best".
The second query will also return rows with the wrong MyForeignID if they happen to have the same MyDate.
Tested this:
if object_id(N'tempdb..#T') is not null
drop table #T
create table #T (
ID int identity primary key,
MyDate datetime,
MyForeignID int);
insert into #T (MyDate, MyForeignID)
select checksum(newid())%1000, checksum(newid())%1000
from dbo.Numbers
where number <= 100000;
create index IDX_T on #T(MyForeignID, MyDate);
set statistics io on;
set statistics time on;
select top 1 *
from #T
where MyForeignID = 400
order by MyDate desc;
select *
from #T
where MyForeignID = 400
and MyDate =
(select max(MyDate)
from #T
where MyForeignID = 400);
Both ended up with 1 millisecond execution time, and with identical execution plans.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 2, 2009 at 5:22 pm
GSquared (2/2/2009)
If there are multiple rows with the same date-time in the MyDateColumn, the first query will only give you one of them, but the second query will give you all of them. That needs to be taken into account when you decide which is "best".The second query will also return rows with the wrong MyForeignID if they happen to have the same MyDate.
Heh... long winded version of...
1. Works correctly
😛
--Jeff Moden
February 2, 2009 at 5:35 pm
[font="Verdana"]Is the MyDateColumn unique? Because if it's not, the second form could return more than one row.
Ooops, really should read all of the replies before posting. Someone already pointed that out.[/font]
February 2, 2009 at 8:26 pm
Bruce W Cassidy (2/2/2009)
[font="Verdana"]Is the MyDateColumn unique? Because if it's not, the second form could return more than one row.Ooops, really should read all of the replies before posting. Someone already pointed that out.[/font]
S'ok... just confirms what the others said.
--Jeff Moden
February 3, 2009 at 7:35 am
Grant Fritchey (2/2/2009)
I agree with Jeff, portable code is a myth.
Oh yeah??!! Well, what if I code on a laptop, huh? Huh?
Thought so.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
February 3, 2009 at 8:09 am
dubem1 (1/31/2009)
What you be the best syntax?I need to get from a table, from a particular set of rows, the one with the max date
Syntax 1:
SELECT TOP 1 * FROM MyTable
WHERE MyForeignIdColumn = 400
ORDER BY MyDateColumn DESC
Syntax 2:
SELECT * FROM MyTable
WHERE MyDateColumn = (SELECT max(MyDateColumn) from MyTable WHERE MyForeignIdColumn = 400)
thank you
Martin
Go with syntax 1.
Syntax 2 is logically incorrect for your requirements. It would not ensure that the row returned is MyForeignIdColumn = 400, and it would not ensure that it returns only one row.
February 3, 2009 at 8:19 am
Code i used was CTE
WITH CTE AS (
SELECT EMPL_ID, effective_dt, ORG,
ROW_NUMBER() OVER(PARTITION BY EMPL_ID ORDER BY effective_dt DESC) AS rn
FROM TABLE)
INSERT INTO TEMPTABLE
SELECT EMPL_ID, effective_dt, ORG
FROM CTE
WHERE rn=1 AND EMPL_ID LIKE 'A156'
February 4, 2009 at 9:50 pm
jcrawf02 (2/3/2009)
Grant Fritchey (2/2/2009)
I agree with Jeff, portable code is a myth.Oh yeah??!! Well, what if I code on a laptop, huh? Huh?
Thought so.
😀
--Jeff Moden
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply