sam sam - 4 months ago 12
SQL Question

How to check multiple values of a single column for each record in sql?

How to check multiple values of a single column for each record in sql?

My table appears like this,

ID Date


----------


1 7/19/2016


----------


1 7/19/2016


----------


1 9/1/2016


----------


2 7/19/2016


----------


2 7/19/2016


----------


2 8/2/2016


I am looking for an output like this,

ID Date


----------


1 7/19/2016


----------


1 9/1/2016


----------


2 7/19/2016


----------


2 8/2/2016


I am looking for result as [Total Id Count] = 4.
Am a tester and new to sql, please help me with this.

Answer

Use Distinct to get distinct values or groups of values in a table.

SELECT DISTINCT Id, Date 
FROM MyTable

To see the latest orders grouped by date and customer, here's a full working example:

Declare @CustomerOrders table(CustomerId int, OrderDate DateTime, OrderId int)
Insert Into @CustomerOrders Values (1, '2016-01-01', 1)
Insert Into @CustomerOrders Values (1, '2016-01-01', 2)
Insert Into @CustomerOrders Values (2, '2016-01-01', 3)
Insert Into @CustomerOrders Values (1, '2016-01-02', 4)

Select * from @CustomerOrders

SELECT o.CustomerId, o.OrderDate, MaxOrder.MaxOrderId
FROM @CustomerOrders o inner join 
    (select o2.CustomerId, o2.OrderDate, max(OrderId) as MaxOrderId 
        From @CustomerOrders o2 
        Group By o2.CustomerId, o2.OrderDate) as MaxOrder 
    on MaxOrder.MaxOrderId = o.OrderId