INFO: I'm dealing with Microsoft SQL.
Ok the title is confusing but here's a good example of the table I am dealing with:
ID Value Signal Read Firmware Date Time 5 123 656 444 217 3/30/2009 11:00:00 AM 5 123 421 333 217 3/30/2009 04:00:00 PM 5 123 111 666 217 3/30/2009 05:00:00 PM 9 321 231 551 216 3/30/2009 09:00:00 AM 9 321 599 887 216 3/30/2009 09:30:00 AM
And So I want the Query to come back:
ID Value Signal Read Firmware Date Time 5 123 111 666 217 3/30/2009 05:00:00 PM 9 321 599 887 216 3/30/2009 09:30:00 AM
SELECT DISTINCT ID, Value, Signal, Read, Firmware, Date, Time FROM ....
But this returns all the results. I've also attempted Choose TOP 1... however i could not have that to operate. I understand this really is simple, but I am confused regarding how to get this display only one single unique row.
Perhaps you have attempted this?
SELECT id, value, MIN(Signal), MIN(Read), MIN(Firmware), MIN(Date), MIN(Time) FROM ... GROUP BY ID, Value
SELECT ID, Value, Signal, Read, Firmware, Date, Time FROM ... GROUP BY ID, Value
The records are distinct, you will find different
Time values. How does one expect the SQL server to guess which you would like?
Your example indicates that you are thinking about the newest record of the given
Id. If that is true, this question should meet your needs:
SELECT table.* FROM table JOIN (SELECT Id, Date, MIN(Time) FROM Table GROUP BY Id, Date) AS selector ON (table.Id = selector.Id AND table.Date = selector.Date and table.Time = selector.Time)
SELECT Id, Value, Signal, Read, Firmware, Date, Time FROM table_name t1 WHERE t1.Id = (SELECT DISTINCT t2.Id FROM table_name t2)
Presuming that there's a cheque to ensure that records with similar Id also provide same return
use TOP 1 WITH TIES
Similar query (uses Mix APPLY and correlated subquery w/ TOP WITH TIES):
SELECT a.CustodianAccountNum, a.AccountName, a.AccountName2, a.AccountName3, a.AccountStartDate, a.AccountClosedDate, a.TaxableFederal, a.TaxableState, qq.ValuationDate, qq.MarketValue, cg.ClientGroupGUID as ClientGUID, c.ClientGUID as EntityGUID, convert (bit, case when b.cnt > 1 then 1 else 0 end) as IsDuplicate FROM ( SELECT a.CustodianAccountNum, MIN(a.AccountID) as AccountID, count(*) as cnt FROM Accounts a WHERE a.AccountID in (SELECT AccountID from dbo.FnGetFilteredAccountIDs(@CurrentUserID)) -- apply permisssions and a.DeletedDate is null group by a.CustodianAccountNum ) b INNER JOIN Accounts a ON a.AccountID = b.AccountID INNER JOIN ClientAccounts ca ON a.AccountID = ca.AccountID and ca.DeletedDate is null INNER JOIN Clients c ON ca.ClientID = c.ClientID and c.DeletedDate is null INNER JOIN ClientGroups cg ON c.ClientGroupID = cg.ClientGroupID and cg.DeletedDate is null CROSS APPLY ( SELECT SUM(MarketValue) as MarketValue, MIN(ValuationDate) as ValuationDate FROM (SELECT TOP 1 WITH TIES arv.MarketValue, arv.ValuationDate FROM AccountReturnValues arv where arv.AccountId = a.AccountId and a.AccountClosedDate is null order by ValuationDate desc ) q ) qq