LAST() in Access and TOP in MSSQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

LAST() in Access and TOP in MSSQL

Author

Topic

benzilla
Starting Member

3 Posts

Posted - 2007-02-11 : 21:48:39

Dear all,
I'm starting a project about storing Printer paper count information into MSSQL 2000 and using PHP to display relevant information in web page.

First, I tried to using MS Access to query the database by using following statement, it displays each printer last print count record.

quote:

SELECT [hp_WJADevice].[Device_Decr], [hp_WJADeviceUtilization].[WJADeviceID], [hp_WJADevice].[Model_Name], Last([hp_WJADeviceUtilization].[CountTime]) AS LastOfCountTime, [hp_WJADevice].[Serial_Nr], [hp_WJADevice].[Asset_Nr], [hp_WJADevice].[IP_Addr], Max([hp_WJADeviceUtilization].[CountValue]) AS MaxOfCountValue
FROM hp_WJACountType INNER JOIN (hp_WJADevice INNER JOIN hp_WJADeviceUtilization ON [hp_WJADevice].[WJADeviceID]=[hp_WJADeviceUtilization].[WJADeviceID]) ON [hp_WJACountType].[WJACountTypeID]=[hp_WJADeviceUtilization].[WJACountTypeID]
GROUP BY [hp_WJADevice].[Device_Decr], [hp_WJADeviceUtilization].[WJADeviceID], [hp_WJADevice].[Model_Name], [hp_WJADevice].[Serial_Nr], [hp_WJADevice].

Referred to [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61419&SearchTerms=LAST()[/url]
I know "LAST" in not designed for MSSQL, but when I tried to change it into "TOP".
However, where I modified those statement in MS SQL Query Analyzer, I found it prompted the following:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TOP'.

Any hints to solve it?

quote:
SELECT WJADevice.Device_Decr, WJADeviceUtilization.WJADeviceID, WJADevice.Model_Name, TOP 1 WJADeviceUtilization.CountTime, WJADevice.Serial_Nr, WJADevice.Asset_Nr, WJADevice.IP_Addr, Max(WJADeviceUtilization.CountValue)
FROM WJACountType INNER JOIN (WJADevice INNER JOIN WJADeviceUtilization ON WJADevice.WJADeviceID = WJADeviceUtilization.WJADeviceID) ON WJACountType.WJACountTypeID = WJADeviceUtilization.WJACountTypeID
GROUP BY WJADevice.Device_Decr, WJADeviceUtilization.WJADeviceID, WJADevice.Model_Name, WJADevice.Serial_Nr, WJADevice.Asset_Nr, WJADevice.IP_Addr, WJACountType.WJACountTypeID
HAVING WJACountType.WJACountTypeID=1
ORDER BY WJADevice.Device_Decr, WJADevice.IP_Addr;

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-11 : 22:00:13

you can't use TOP this way.

the syntax is


select top 1 col1, col2, col3
from   table
order by somecol

try this to see if this is what you want ?


SELECT TOP 1 WJADevice.Device_Decr, WJADeviceUtilization.WJADeviceID, WJADevice.Model_Name, TOP 1  WJADeviceUtilization.CountTime, WJADevice.Serial_Nr, WJADevice.Asset_Nr, WJADevice.IP_Addr, Max(WJADeviceUtilization.CountValue)
FROM WJACountType INNER JOIN (WJADevice INNER JOIN WJADeviceUtilization ON WJADevice.WJADeviceID = WJADeviceUtilization.WJADeviceID) ON WJACountType.WJACountTypeID = WJADeviceUtilization.WJACountTypeID
GROUP BY WJADevice.Device_Decr, WJADeviceUtilization.WJADeviceID, WJADevice.Model_Name, WJADevice.Serial_Nr, WJADevice.Asset_Nr, WJADevice.IP_Addr, WJACountType.WJACountTypeID
HAVING WJACountType.WJACountTypeID=1
ORDER BY WJADevice.Device_Decr, WJADevice.IP_Addr

or is it the max of WJADeviceUtilization.CountTime that you want ?


SELECT WJADevice.Device_Decr, WJADeviceUtilization.WJADeviceID, WJADevice.Model_Name, TOP 1 max(WJADeviceUtilization.CountTime), WJADevice.Serial_Nr, WJADevice.Asset_Nr, WJADevice.IP_Addr, Max(WJADeviceUtilization.CountValue)
FROM WJACountType INNER JOIN (WJADevice INNER JOIN WJADeviceUtilization ON WJADevice.WJADeviceID = WJADeviceUtilization.WJADeviceID) ON WJACountType.WJACountTypeID = WJADeviceUtilization.WJACountTypeID
GROUP BY WJADevice.Device_Decr, WJADeviceUtilization.WJADeviceID, WJADevice.Model_Name, WJADevice.Serial_Nr, WJADevice.Asset_Nr, WJADevice.IP_Addr, WJACountType.WJACountTypeID
HAVING WJACountType.WJACountTypeID=1
ORDER BY WJADevice.Device_Decr, WJADevice.IP_Addr;

If not, may by you can explain a bit on what the query is doing.

benzilla
Starting Member

3 Posts

Posted - 2007-02-11 : 22:25:04

Dear KH,
Thanks for your kindly teaching, I used the second statement and it works.
BTW, I want to clarify, is that mean "TOP" can be used in first selected column only?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-11 : 22:33:05

TOP in SQL Server is not equal to the LAST in access. TOP does not apply to the first selected column. TOP apply to the selected records. example TOP 4 will give you first 4 records as specify by your 'ORDER BY' sequence.

benzilla
Starting Member

3 Posts

Posted - 2007-02-11 : 22:53:29

OIC, thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources