Menu
I have the following query:
select
C.ClientID,
C.FirstName + ' ' + C.LastName as ClientName,
CAST(V.StartDate as date) as VisitDate,
count(*) as 'Number of Visits'
from
Visit V
Inner Join Client C on
V.ClientID = C.ClientID
group by
C.ClientID,
C.FirstName + ' ' + C.LastName,
CAST(V.StartDate as date)
having
count(*) > 3
order by
C.ClientID,
CAST(V.StartDate as date)
which gives the following results (names are fake in case anyone is wondering)
ClientID ClientName VisitDate Number of Visits
75 Kay Taylor 2016-06-07 4
372 Moses Mcgowan 2016-09-03 4
422 Raven Mckay 2016-03-11 4
422 Raven Mckay 2016-06-14 4
679 Ulysses Booker 2016-01-09 4
696 Timon Turner 2016-07-06 4
1063 Quyn Wall 2016-06-25 4
1142 Garth Moran 2016-11-20 4
1142 Garth Moran 2016-11-21 4
1563 Hedley Gutierrez 2016-01-07 4
1563 Hedley Gutierrez 2016-01-17 4
1563 Hedley Gutierrez 2016-01-21 4
1563 Hedley Gutierrez 2016-01-27 4
1563 Hedley Gutierrez 2016-01-28 4
1563 Hedley Gutierrez 2016-01-30 4
1563 Hedley Gutierrez 2016-02-27 4
1563 Hedley Gutierrez 2016-03-26 4
1563 Hedley Gutierrez 2016-04-06 4
1563 Hedley Gutierrez 2016-04-09 4
1563 Hedley Gutierrez 2016-04-22 4
1563 Hedley Gutierrez 2016-05-06 4
1563 Hedley Gutierrez 2016-05-26 4
1563 Hedley Gutierrez 2016-06-02 4
1563 Hedley Gutierrez 2016-07-14 4
1563 Hedley Gutierrez 2016-07-29 4
1563 Hedley Gutierrez 2016-08-09 7
1563 Hedley Gutierrez 2016-09-01 4
1563 Hedley Gutierrez 2016-09-23 4
1563 Hedley Gutierrez 2016-12-07 4
1636 Kiara Lowery 2016-01-12 4
2917 Cynthia Carr 2016-06-21 4
2917 Cynthia Carr 2016-10-21 4
3219 Alan Monroe 2016-01-02 4
3219 Alan Monroe 016-02-27 4
3219 Alan Monroe 2016-09-01 5
4288 Natalie Mitchell 2016-03-19 4
How can I get the results to show only the ClientID and ClientName once so the results are like this?
ClientID ClientName VisitDate Number of Visits
75 Kay Taylor 2016-06-07 4
372 Moses Mcgowan 2016-09-03 4
422 Raven Mckay 2016-03-11 4
2016-06-14 4
679 Ulysses Booker 2016-01-09 4
696 Timon Turner 2016-07-06 4
1063 Quyn Wall 2016-06-25 4
1142 Garth Moran 2016-11-20 4
2016-11-21 4
1563 Hedley Gutierrez 2016-01-07 4
2016-01-17 4
2016-01-21 4
2016-01-27 4
2016-01-28 4
2016-01-30 4
2016-02-27 4
2016-03-26 4
2016-04-06 4
2016-04-09 4
2016-04-22 4
2016-05-06 4
2016-05-26 4
2016-06-02 4
2016-07-14 4
2016-07-29 4
2016-08-09 7
2016-09-01 4
2016-09-23 4
2016-12-07 4
1636 Kiara Lowery 2016-01-12 4
2917 Cynthia Carr 2016-06-21 4
2016-10-21 4
3219 Alan Monroe 2016-01-02 4
3219 016-02-27 4
2016-09-01 5
4288 Natalie Mitchell 2016-03-19 4
295
SELECT [ALL | DISTINCT] columns FROM table; If a table has a properly defined primary key, SELECT DISTINCT * FROM table; and SELECT * FROM table; return identical results because all rows are unique.
Answer: The DISTINCT keyword is used to remove the duplicate rows in a table.
- select ...; delete from ... where id_specific_price in (...) These aren't duplicate rows, you're deleting based on something subtler than that. You're asking to delete all but one row (the one with the highest id in one column) from a set grouped on a second id column.
Actually, what you want is not to remove duplicates, but not display them.
In order to do this you can use a CASE statement with ROW_NUMBER() and show the value on the 1st row and display either NULL or '' on the ELSE branch (the other rows):
select
CASE
WHEN ROW_NUMBER() OVER (PARTITION BY C.ClientID ORDER BY CAST(V.StartDate as date) ASC) = 1
THEN C.ClientID
ELSE NULL
END as ClientID,
CASE
WHEN ROW_NUMBER() OVER (PARTITION BY C.ClientID ORDER BY CAST(V.StartDate as date) ASC) = 1
THEN C.FirstName + ' ' + C.LastName
ELSE NULL
END as ClientName,
CAST(V.StartDate as date) as VisitDate,
count(*) as 'Number of Visits'
from
Visit V
Inner Join Client C on
V.ClientID = C.ClientID
group by
C.ClientID,
C.FirstName + ' ' + C.LastName,
CAST(V.StartDate as date)
having
count(*) > 3
order by
C.ClientID,
CAST(V.StartDate as date)
Try this:
DECLARE @Table TABLE (ClientId NVARCHAR(5), ClientName NVARCHAR(6), VisitDate DATE, NumOfVisits INT)
INSERT INTO @Table VALUES ('75' , 'A_NAME' , '2016-06-07' , '4' ),('372' , 'B_NAME' , '2016-09-03' , '4' ),
('422' , 'C_NAME' , '2016-03-11' , '4' ),('500' , 'D_NAME' , '2016-03-15' , '4'),
('500' , 'D_NAME' , '2016-03-19' , '4' ),('500' , 'D_NAME' , '2016-03-20' , '4'),
('500' , 'D_NAME' , '2016-07-15' , '4' ),('500' , 'D_NAME' , '2016-09-13' , '4'),
('600' , 'E_NAME' , '2016-03-19' , '4' ),('600' , 'E_NAME' , '2016-03-20' , '4'),
('600' , 'E_NAME' , '2016-07-15' , '4' ),('600' , 'E_NAME' , '2016-09-13' , '4')
;WITH A AS (
SELECT ROW_NUMBER() OVER(PARTITION BY ClientID ORDER BY ClientID) row_id,* FROM (
-----------------------------------------
SELECT * FROM @Table --- replace this line with your query----
-----------------------------------------
) Main_Result ) SELECT ISNULL(BB.ClientID,'')ClientID,ISNULL(BB.ClientName,'')ClientName,AA.VisitDate,AA.NumOfVisits
FROM A AA LEFT JOIN (SELECT * FROM A BB WHERE BB.row_id=1) BB ON AA.ClientID = BB.ClientID AND AA.row_id =BB.row_id
ORDER BY CONVERT(INT,AA.ClientID)
Hope this helps. :)
you can execute this directly to get sample result from sample data. :)
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
