FIRST method in SQL Server not recognized

[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

 

[Jacek Segit]

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

 

[Guest]

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

 

[Jacek Segit]

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

 

[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