FIRST method in SQL Server not recognized

  • Thread starter Thread starter subir.kumar
  • Start date Start date
S

subir.kumar

Hi,

I have just migrated from Access to SQL Server. In access
I had queries which used FIRST,but when I use the same in
SQL Server it says "first is not a recognized function
name". Can anyone please advise me an equivalent method in
SQL Server.

Thank you for your time.

Regards,

Subir
 
Witaj subir,
W Twoim liœcie datowanym 18 maja 2004 (19:47:39) mo¿na przeczytaæ:

skcc> Hi,
skcc> I have just migrated from Access to SQL Server. In access
skcc> I had queries which used FIRST,but when I use the same in
skcc> SQL Server it says "first is not a recognized function
skcc> name". Can anyone please advise me an equivalent method in
skcc> SQL Server.

Its the same in both, Access and MSSQL:
SELECT TOP 1 ... FROM ... WHERE ....

Where do you want to use this function?

By the way:
Remember that FIRST/LAST in Access return RANDOM record
(relational-model do not know what is first and last, any time any
record can be first or last, it depends on sorting)

Regards
Jacek Segit
 
Jacek,

The Query in Access is

SELECT qryDatafile2003.[Product Code], qryDatafile2003.
[Sub Product Code], tblExpenseCodes.[Report Client], First
(qryDatafile2003.[Product Name]) AS [FirstOfProduct Name],
First(qryDatafile2003.[Sub Product Name]) AS [FirstOfSub
Product Name]
FROM qryDatafile2003 INNER JOIN tblExpenseCodes ON
qryDatafile2003.[Long Code] = tblExpenseCodes.[long code]
GROUP BY qryDatafile2003.[Product Code], qryDatafile2003.
[Sub Product Code], tblExpenseCodes.[Report Client]

Thanks,

Subir
 
Witaj anonymous,
W Twoim li¶cie datowanym 20 maja 2004 (23:47:30) mo¿na przeczytaæ:

admc> Jacek,
admc> The Query in Access is
admc> SELECT qryDatafile2003.[Product Code], qryDatafile2003.
admc> [Sub Product Code], tblExpenseCodes.[Report Client], First
admc> (qryDatafile2003.[Product Name]) AS [FirstOfProduct Name],
admc> First(qryDatafile2003.[Sub Product Name]) AS [FirstOfSub
admc> Product Name]
admc> FROM qryDatafile2003 INNER JOIN tblExpenseCodes ON
admc> qryDatafile2003.[Long Code] = tblExpenseCodes.[long code]
admc> GROUP BY qryDatafile2003.[Product Code], qryDatafile2003.
admc> [Sub Product Code], tblExpenseCodes.[Report Client]


SELECT qryDatafile2003.[Product Code], qryDatafile2003.
[Sub Product Code], tblExpenseCodes.[Report Client],
qryDatafile2003.[Product Name] AS [FirstOfProduct Name],
qryDatafile2003.[Sub Product Name] AS [FirstOfSub
Product Name]
FROM qryDatafile2003 INNER JOIN tblExpenseCodes ON
qryDatafile2003.[Long Code] = tblExpenseCodes.[long code]
GROUP BY qryDatafile2003.[Product Code], qryDatafile2003.
[Sub Product Code], tblExpenseCodes.[Report Client]
ORDER BY [FirstOfProduct Name] ASC, [FirstOfSub
Product Name] ASC

(I hope it works)
Regards
Jacek Segit
 
Witaj Jacek,
W Twoim li¶cie datowanym 22 maja 2004 (11:30:59) mo¿na przeczytaæ:

JS> SELECT qryDatafile2003.[Product Code], qryDatafile2003.
JS> [Sub Product Code], tblExpenseCodes.[Report Client],
JS> qryDatafile2003.[Product Name] AS [FirstOfProduct Name],
JS> qryDatafile2003.[Sub Product Name] AS [FirstOfSub
JS> Product Name]
JS> FROM qryDatafile2003 INNER JOIN tblExpenseCodes ON
JS> qryDatafile2003.[Long Code] = tblExpenseCodes.[long code]
JS> GROUP BY qryDatafile2003.[Product Code], qryDatafile2003.
JS> [Sub Product Code], tblExpenseCodes.[Report Client]
JS> ORDER BY [FirstOfProduct Name] ASC, [FirstOfSub
JS> Product Name] ASC

JS> (I hope it works)

No it not works correctly :-)

General vision:
Select
*
From
DS
Inner Join
(Select
UserId,
Max(DataOd) As Dm
From
Ds) As M
On
Ds.UserId = M.UserId
And
Ds.DataOd = M.Dm

But I have to know, which field is your "Unique" (in this context)
And put sample return data you want to get

Regards
Jacek Segit
 
Back
Top