CODEWITHSUNDEEP
sql
Kara
Kara

Reputation: 55

How to get column that has more than one record

in SQL I have this currently:

Checklist Name | Version

Checklist A     1.1
Checklist A     2.1
Checklist B     1.1
Checklist C     1.1
Checklist C     2.1
Checklist C     3.1

I am trying to get only the records that have more than one version (so NOT checklist B). This is my desired outcome:

Checklist Name | Version

Checklist A     1.1
Checklist A     2.1
Checklist C     1.1
Checklist C     2.1
Checklist C     3.1

I am having trouble with this because I tried using a having clause and it is only letting me select the checklist name, so this would work

select checklistname
from checklisttable
group by checklistname
having count(version) > 1

The above would product this output:

Checklist A
Checklist C

But this (which is what I need cause I want to see the versions, DOESN'T work):

select checklistname, version
from checklisttable
group by checklistname, version
having count(version) > 1

This returns 0 records.

Thank you so much for any help!

Upvotes: 0

Views: 74

Answers (2)

Mat Richardson
Mat Richardson

Reputation: 3606

You could use window functions to do this:-

select * from
(
select
    Checklist_Name,
    Version,
    count(Checklist_Name) over(partition by Checklist_Name) c
from checklisttable
) data
where c > 1

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

You have to use your query as a derived table and join it back to the original table to get the rest of the fields:

select t1.*
from checklisttable t1
join (
  select checklistname
  from checklisttable
  group by checklistname
  having count(version) > 1
) t2 on t1.checklistname = t2.checklistname

Upvotes: 2

Related Questions