473,327 Members | 1,967 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

T-SQL Question

How to write an equivalent SQL like this (which works on Oracle):

SELECT * FROM sometable
WHERE rec_id, rec_sequence
IN (SELECT rec_id, MAX( rec_sequence) FROM alppremh GROUP BY rec_id)

thanks
Nov 12 '06 #1
3 1386
>
SELECT * FROM sometable
WHERE rec_id, rec_sequence
IN (SELECT rec_id, MAX( rec_sequence) FROM alppremh GROUP BY rec_id)

thanks
edit:

SELECT * FROM sometable
WHERE rec_id, rec_sequence
IN (SELECT rec_id, MAX( rec_sequence) FROM alppremh GROUP BY rec_id)
and rec_id = 4883493
Nov 12 '06 #2
SELECT s.*
FROM sometable s
INNER JOIN
(
SELECT rec_id,
rs = MAX(rec_sequence)
FROM alppremh
GROUP BY rec_id
) a
ON s.rec_id = a.rec_id
AND s.rec_sequence = a.rs;

"klabu" <noone_at_gmail_dot_comwrote in message
news:12*************@corp.supernews.com...
How to write an equivalent SQL like this (which works on Oracle):

SELECT * FROM sometable
WHERE rec_id, rec_sequence
IN (SELECT rec_id, MAX( rec_sequence) FROM alppremh GROUP BY rec_id)

thanks

Nov 12 '06 #3
[responding only to comp.databases.ms-sqlserver, due to newsreader limitations]

Another option, probably more efficient in most cases, is

select * from sometable
where rec_sequence = (
select max(rec_sequence)
from alppremh
where rec_id = 4883493
)
and rec_id = 4883493

Steve Kass
Drew University
http://www.stevekass.com

klabu wrote:
How to write an equivalent SQL like this (which works on Oracle):

SELECT * FROM sometable
WHERE rec_id, rec_sequence
IN (SELECT rec_id, MAX( rec_sequence) FROM alppremh GROUP BY rec_id)
and rec_id = 4883493
>
Nov 15 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Steve | last post by:
Hi; I'm brand spanking new to sqlserver ( nice so far ). I need to make a simple data change across a list of tables. Basically replace an old date with a new date. However, the people I am...
2
by: Steve | last post by:
Hi; I have been writing a lot of short tsql scripts to fix a lot of tiny database issues. I was wondering if a could make an array of strings in tsql that I could process in a loop, something...
18
by: mountain man | last post by:
Greetings to all database professionals and laymen, Let us make a bold assumption that we have developed a software tool for the SQL Server environment which simply acts as an interface between...
2
by: dynoweb | last post by:
I have several *.sql files with schema/data changes to be applied to our current database. Is there a way to create a TSQL script that could be run from the SQL Query Analyzer that would...
1
by: TOM GUGGER | last post by:
OMNI GROUP tgugger@aimexec.com T-SQL/ CONTRACT TO PERM/ ATLANTA
3
by: David Lozzi | last post by:
Howdy, ISSUE 1: See issue 2 below. I have a distance calculator on my site which works great. However, the users need to sort by distance, which make sense. I'm not sure how to do it other than...
16
by: David Lozzi | last post by:
Hello, I have some code that adds a new user. The new user has a checkboxlist of items which they can be associated with. I would like to send this list of items to TSQL along with the new user...
7
by: Filips Benoit | last post by:
Dear all, Tables: COMPANY: COM_ID, COM_NAME, ..... PROPERTY: PRP_ID, PRP_NAME, PRP_DATATYPE_ID, PRP_DEFAULT_VALUE ( nvarchar) COMPANY_PROPERTY: CPROP_COM_ID, CPROP_PRP_ID, CPROP_VALUE...
8
by: David Lozzi | last post by:
I'm fairly new to ASP.Net 2.0 SQLDatasource objects. It defaults using TSQL statments for the SELECT, INSERT, UPDATE, DELETE commands, which is great and it works. However, I've always been taught...
0
by: DR | last post by:
Unable to start TSQL Debugging. Could not attach to SQL Server Process on 'srvname'. The RPC server is unavailable. I get this error when I try to run a SQL Server Project with a CLR stored...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.