Hepl on SQL !!!!!!!

I am trying to get the most recent info on say, billetedid 1, regardless of its availability is 0 of 1. (In other words, I want to get a unique billet for cstunitd = 69)
But I also want to know what its availability.

SQL1 gives me un-unique values but gives me the value of the availability that I need.
SQL2 gives me the uniqueness of values that I want but does not gives me the value of the availability that I need.

How can I get uniqueness and also the corresponding availability values?

SQL1:
SELECT cstunitid, billetid, cast(availability as int) as availability,
MAX(datetimestamp) as datetimestamp
FROM DM_Bllet_Assignment_Details_view
where datetimestamp is not null and cstunitid = 69
GROUP BY cstunitid, billetid,cast(availability as int)
order by cstunitid,billetid

RESULTS1:
cstunitid billetid availability datetimestamp
---- ---- ---- --------
69 1 1 2002-11-18 14:04:55.000
69 1 0 2002-11-21 00:17:22.000
69 2 1 2002-11-18 14:04:55.000
69 2 0 2002-11-21 00:17:22.000
69 3 1 2002-11-21 00:17:22.000
69 3 0 2002-11-18 14:04:34.000
69 4 1 2002-11-21 00:17:22.000
69 5 1 2002-11-21 00:17:22.000

SQL2:
SELECT cstunitid, billetid,
MAX(datetimestamp) as datetimestamp
FROM DM_Bllet_Assignment_Details_view
where datetimestamp is not null and cstunitid = 69
GROUP BY cstunitid, billetid
order by cstunitid,billeted

RESULTS2:
cstunitid billetid datetimestamp
---- ---- --------
69 1 2002-11-21 00:17:22.000
69 2 2002-11-21 00:17:22.000
69 3 2002-11-21 00:17:22.000
69 4 2002-11-21 00:17:22.000
69 5 2002-11-21 00:17:22.000Surely it should be sufficient just to do a max(availability):

SELECT cstunitid, billetid,
max(availability) as availability
MAX(datetimestamp) as datetimestamp
FROM DM_Bllet_Assignment_Details_view
where datetimestamp is not null and cstunitid = 69
GROUP BY cstunitid, billetid
order by cstunitid,billeted|||If I use that query, I will always end up with a "1" in cases where there are "1" and "0". I do not want that; I want to be able to pick up the status as "0" if the most recent status is "0".

Thanks for your response.|||OK, I understand. Then you need a 2 stage process:

1. identify the latest record for each cstunitid, billetid

SELECT cstunitid, billetid,
MAX(datetimestamp) as datetimestamp
into #t1
FROM DM_Bllet_Assignment_Details_view
where datetimestamp is not null and cstunitid = 69
GROUP BY cstunitid, billetid

2. Get the availability for that latest record

SELECT a.cstunitid, a.billetid,a.availability
FROM DM_Bllet_Assignment_Details_view a, #t1 b
where a.cstunitid=a.cstunitid
and a.billetid=b.billetid
and a.datetimestamp=b.datetimestamp
order by cstunitid,billeted

Note: if there's any chance of two records with the same timestamp you might want to create a new time-ordered key using identity(int,1,1) and use this instead of the datetimestamp.|||Thanks a lot andyabel. Before I try what you have just suggested, I want to ask whether I should create the views or table #t1 first. I using views and the database is SQL Server.|||Hi. If you use "select ... into tablename from ..." SQL Server creates a table for you automatically (it works out what fields and types you need). All you have to do is remember to drop it when you've finished.
However, if you give the table a name beginning with '#' it makes it a temporary table and SQL Server will actually drop the table for you at the end of the batch.|||When I now run

SELECT a.cstunitid, a.billetid,a.availability
FROM DM_Bllet_Assignment_Details_view a, #t1 b
where a.cstunitid=b.cstunitid
and a.billetid=b.billetid
and a.datetimestamp=b.datetimestamp
order by cstunitid,billetid

I get an error message? Aaaaargh!!!!|||um, you need neither a view nor a temp table, one query will do it
select A.cstunitid
, A.billetid
, cast(A.availability as int) as availability
, A.datetimestamp
from DM_Bllet_Assignment_Details_view as A
inner
join ( select cstunitid
, billetid
, max(datetimestamp) as maxdatetime
from DM_Bllet_Assignment_Details_view
where datetimestamp is not null
and cstunitid = 69
group by cstunitid
, billetid ) as B
on A.cstunitid = B.cstunitid
and A.billetid = B.billetid
and A.datetimestamp = B.maxdatetime
order
by A.cstunitid
, A.billetid

caution: not tested (obviously)

rudy|||Well, my script parses now:

set transaction isolation level read uncommitted
drop table DM_Bllet_Assignment_Details_view
drop table #t1

create table DM_Bllet_Assignment_Details_view
(
cstunitid smallint,
billetid smallint,
availability smallint,
datetimestamp datetime
)
go

SELECT cstunitid, billetid,
MAX(datetimestamp) as datetimestamp
into #t1
FROM DM_Bllet_Assignment_Details_view
where datetimestamp is not null and cstunitid = 69
GROUP BY cstunitid, billetid
go

SELECT a.cstunitid, a.billetid, availability
FROM DM_Bllet_Assignment_Details_view a inner join #t1 b
on a.cstunitid=b.cstunitid
and a.billetid=b.billetid
and a.datetimestamp=b.datetimestamp
order by a.cstunitid, a.billetid
go