SQL Server CE retrieve a field from the record with the MAX(Id)

Question

I am using SQL Server Compact 4.0 and am trying to get one query to return a single record with a column Alarm based on the maximum identity when another column Cleared is NULL.

This works:

SELECT Id, Alarm 
FROM Alarm_History 
WHERE Cleared IS NULL

Giving me half the answer. The problem is that it returns several records.

This also works:

SELECT MAX(Id) 
FROM Alarm_History 
WHERE Cleared IS NULL

And it gives me the other half of the answer, but I can't get the value "Alarm" that I am looking for.

But they DON'T work together as in:

SELECT Id, Alarm 
FROM Alarm_History 
WHERE Cleared IS NULL AND Id = MAX(Id)

OR

SELECT MAX(Id), Alarm 
FROM Alarm_History 
WHERE Cleared IS NULL

With the queries above I can do two consecutive queries to get the result back, but I don't think this is very efficient. Is there a way to do this in one trip to the database?

Thanks Jeff

"With the queries above I can do two consecutive queries to get the result back, but I don't think this is very efficient. " yet you accept the answer with two queries. — P. Camilleri, Apr 27 '14 at 18:23

score 1 · Answer 1 · answered Apr 27 '14 at 18:07

1

Don't think "max". Think "order by". Try this:

SELECT TOP 1 Id, Alarm
FROM Alarm_History
WHERE Cleared IS NULL
ORDER BY Id DESC;

That way, you can get all the fields you want associated with the max id.

answered Apr 27 '14 at 18:07

Gordon Linoff

1,242,037
58
646
786

This works as well as the answer not using "TOP" and not having to sort. I can't tell which is actually more efficient and I appreciate the answer. – Jeff Patterson Apr 27 '14 at 18:26

P. Camilleri · Accepted Answer · 2014-04-27T18:15:18.033

1

You could do it with max and a join, but that would be far too complicated. Instead, you can just try:

SELECT TOP 1 Id, Alarm
FROM Alarm_History
WHERE Cleared IS NULL
ORDER BY Id DESC

Note: what happens if two alarms have max id ? Well, that shouldn't happen (Id is unique). But that's an interesting problem nevertheless (suppose the field is not Id but Price, and you want all alarms with max price, and you don't know how many there'll be, so you cannot use TOP 1 anymore). You'll have to use a CROSS JOIN;

SELECT Id, Price
FROM Alarms
JOIN (SELECT MAX(Price) as maxPrice FROM Alarms) b
WHERE Alarms.Price = b.maxPrice

edited Apr 27 '14 at 18:15

answered Apr 27 '14 at 18:08

P. Camilleri

12,664
7
41
76

Sorry, used MySql syntax. Edited. – P. Camilleri Apr 27 '14 at 18:11
Does the answer I previously accepted take two trips to the database to get the result I am looking for? If so what you are saying makes perfect sense to me and I think I am beginning to understand. In my original question I was concerned with writing two queries and then having to manage the results separately. That seemed like a lot of unnecessary code and I like your answer more. – Jeff Patterson Apr 27 '14 at 19:21
@JeffPatterson see http://stackoverflow.com/questions/761150/how-does-the-in-predicate-work-in-sql?lq=1 – P. Camilleri Apr 29 '14 at 13:37

score 0 · Answer 3 · answered Apr 27 '14 at 18:09

0

Try,

SELECT Id, Alarm FROM Alarm_History WHERE ID in (SELECT MAX(Id) FROM Alarm_History WHERE Cleared IS NULL)

answered Apr 27 '14 at 18:09

M22an

1,242
1
18
35

SQL Server CE retrieve a field from the record with the MAX(Id)

3 Answers3