SQL Server Performance Forum – Threads Archive

Select First

How would I go about selecting the following: Table: Client, RecordType, RecordDate Query: Select Distinct Client From tblTable Where
(FIRST client record is between Date1 and Date2)

---

To be honest I’m not 100% sure I know what you mean (then again it could be only me!).
If you want each distinct client record between 2 dates you could use the following : SELECT DISTINCT Client FROM tblTable WHERE
RecordDate BETWEEN Date1 AND Date2 If there was some kind of order on which you want to base your search say now the RecordDate then you could create a temporary table holding all the Clients and the RecordDate with a RecordDate between your 2 dates in an ascending or descending order and then use the temporary table in a subselect to select the distinct Client records where the Client is in the temporary table. I’m a little confused to be honest, anyone else got some suggestions? Cheers
Shaun
World Domination Through Superior Software

---

I don’t really understand either, i was thinking more of select distinct client from (select top 1 client from table where RecordDate between date and date2) but in that case the distinct is redundant and you only need the 2nd select. Tom Pullen
DBA, Oxfam GB

---

If you use TOP 1 then I think you’ll only ever get 1 record back.
I thought he meant that for each client he only wants one record back and that record must be the first record between 2 dates, what I think is missing though is the order from which you select the first record? Cheers
Shaun World Domination Through Superior Software

---

I think he wants all clients where the earliest date for each client is in the given date parameters Perhaps try this.. select distinct Client from MyTable BaseTable
where RecordDate =
(select MIN(RecordDate) from MyTable InnerTable where InnerTable.Client = BaseTable.Client) and
BaseTable.RecordDate >= ‘2001-01-01’ and
BaseTable.RecordDate <= ‘2003-01-01’

---

]]>