October 4, 2007 at 6:13 am
hi all.
i have this problem..
i have a query that contains the LAST function that extract right results in MS Access but i cannot obtain the same result in MS SQL Server.
i know that LAST and FIRST function are MS Access only so.. how can i replace these features in SQL Server?
i'll give an example.
i have 2 rows with this content data:
COMUNE DI MILANO | Sede Generale | 2 | C | 10 | 0
COMUNE DI MILANO | Piazza | 2 | C | 10 | 1
fields are:
city | reference | phase | section | progressive | revision
how can select the row with the max revision?
all fields are grouped.
the 2 rows differ by reference and revision fields so GROU BY gives me 2 rows.
in MS Access i use MAX(revision) and LAST for every other fields.
in SQL Server????
thank's all in advance.
October 4, 2007 at 6:56 am
SELECT TOP 1 *
FROM X
ORDER BY Something (ASC/DESC)
The ASC (ascending) will get you the first record, the DESC (descending) will get you the last record. Just remember that TOP processes all records in the query and only returns the number of rows specified. It doesn't change the amount of data processed.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 4, 2007 at 7:25 am
it's not so simple..:P
i wrote only a small portion of data..
a real table should have many records like this format:
COMUNE DI MILANO | Sede Generale | 2 | C | 10 | 0
COMUNE DI MILANO | Piazza | 2 | C | 10 | 1
COMUNE DI MILANO | Via Roma| 2 | C | 10 | 2
COMUNE DI ROMA | Sede Generale | 2 | C | 10 | 0
COMUNE DI ROMA | Piazza | 2 | C | 10 | 1
COMUNE DI ROMA | Via Roma| 2 | C | 10 | 1
look at the 2 numer 1 in last field.
i want a query that returns this:
COMUNE DI MILANO | Via Roma| 2 | C | 10 | 2
COMUNE DI ROMA | Piazza | 2 | C | 10 | 1
COMUNE DI ROMA | Via Roma| 2 | C | 10 | 1
only the last revision for every document.
so.. i have the rev. 2 for the first and rev. 1 for the second that it's owned by 2 rows.
i think it's much clear now.
😀
thanks guys!
October 4, 2007 at 8:06 am
Oh, OK. Well it sounds similar to a problem we have
SELECT ...
FROM p
CROSS APPLY (SELECT TOP 1 ...
FROM v
WHERE t.Keystructure = v.keystructure
ORDER BY v.OrderingColumn ASC/DESC)
You can simply join the table to itself for the same results. Works great.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 4, 2007 at 8:56 am
oh my god!
may be it's too difficult for me..
this is the function i've created:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER FUNCTION [dbo].[fn_UltimaRevisione](@id_commessa AS int, @cod_fase AS int, @cod_sezione AS nvarchar, @cod_progressivo AS int, @n AS INT)
RETURNS TABLE
AS
RETURN
SELECT TOP(@n) *
FROM com_doc_emessi
WHERE id_commessa = @id_commessa AND cod_fase=@cod_fase AND cod_sezione=@cod_sezione AND cod_progressivo=@cod_progressivo
ORDER BY cod_revisione DESC
and this is the query:
SELECT com_doc_emessi.id_commessa, anagrafiche.rag_sociale, contatti.cognome, com_doc_emessi.cod_fase, com_doc_emessi.cod_sezione, com_doc_emessi.cod_progressivo, com_doc_emessi.cod_revisione, UR.cod_revisione
FROM contatti INNER JOIN
prot_intestatari ON contatti.id = prot_intestatari.id_contatto INNER JOIN
prot_registro ON prot_intestatari.id_protocollo = prot_registro.ID INNER JOIN
com_doc_emessi INNER JOIN
prot_doc_trasmessi ON com_doc_emessi.id = prot_doc_trasmessi.documento ON prot_registro.ID = prot_doc_trasmessi.id_protocollo INNER JOIN
anagrafiche ON contatti.id_anagrafica = anagrafiche.id
CROSS APPLY fn_UltimaRevisione(com_doc_emessi.id_commessa,com_doc_emessi.cod_fase, com_doc_emessi.cod_sezione, com_doc_emessi.cod_progressivo,1) AS UR
WHERE (com_doc_emessi.id_commessa = 122)
GROUP BY com_doc_emessi.id_commessa, com_doc_emessi.cod_fase, com_doc_emessi.cod_sezione, com_doc_emessi.cod_progressivo, anagrafiche.rag_sociale, com_doc_emessi.cod_revisione, UR.cod_revisione, contatti.cognome
can you take a look to verify if it's all right?
i have a strage result:
i get all the rows with the column REVISIONE (the rev. of the doc) with the right number but i get rows with wrong rev. too!
using the previous example data, i get:
COMUNE DI MILANO | Sede Generale | 2 | C | 10 | 2
COMUNE DI MILANO | Piazza | 2 | C | 10 | 2
COMUNE DI MILANO | Via Roma| 2 | C | 10 | 2
COMUNE DI ROMA | Sede Generale | 2 | C | 10 | 1
COMUNE DI ROMA | Piazza | 2 | C | 10 | 1
COMUNE DI ROMA | Via Roma| 2 | C | 10 | 1
may be "the light" it's just around the corner 😀
thank you very much!
October 4, 2007 at 9:16 am
Keep in mind - you can use MAX and MIN functions on character fields, and they will return the char field. Might be easier than doing the link to itself thing.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 4, 2007 at 9:17 am
I'd stay away from table valued functions unless performance is completely not a concern.
The cross apply should only return a single value such that you'd get more than one row, but they'd all have the same top value, based on the function you've got. But the function doesn't act to limit values in the right way.
How about something like:
SELECT..
FROM...
INNER JOIN com_doc_emessi
ON
FROM ...JOIN com_doc_emessi c
ON keystructure = keystructure
AND
cod_revisione = (SELECT TOP 1
FROM com_doc_emessi c2
WHERE id_commessa = @id_commessa
AND cod_fase=@cod_fase
AND cod_sezione=@cod_sezione
AND cod_progressivo=@cod_progressivo
ORDER BY cod_revisione DESC)
WHERE ...
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 4, 2007 at 9:43 am
keystructure is my id column? the identity field?
October 4, 2007 at 9:47 am
Yeah. I put it that way in case, like us, you have a compound PK.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 4, 2007 at 9:51 am
it doesn't work..
syntax error near keyword "FROM"
may be there are problems with inner join chain but i can't find the error..
FROM contatti INNER JOIN
prot_intestatari ON contatti.id = prot_intestatari.id_contatto INNER JOIN
prot_registro ON prot_intestatari.id_protocollo = prot_registro.ID INNER JOIN
com_doc_emessi
AS C on cod_revisione =(
SELECT TOP 1
FROM com_doc_emessi AS c2
WHERE id_commessa = @id_commessa
AND cod_fase=@cod_fase
AND cod_sezione=@cod_sezione
AND cod_progressivo=@cod_progressivo
ORDER BY cod_revisione DESC
)
INNER JOIN
prot_doc_trasmessi ON com_doc_emessi.id = prot_doc_trasmessi.documento ON prot_registro.ID = prot_doc_trasmessi.id_protocollo INNER JOIN
anagrafiche ON contatti.id_anagrafica = anagrafiche.id
October 4, 2007 at 10:13 am
it works!!!
you are a genius!!!!
my idol!!!!!!!
:w00t:
thank you very much!!!
:hehe:
October 4, 2007 at 12:00 pm
You got it? Cool. It's a neat little trick that I learned from a Microsoft Consultant. It's good to see other people find it useful too.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply