Newb sql question

[jinduy]

say you have a list of employees with their names...

let's say there's two guys named David

how would you make a query where it only displays duplicate names, such as David's?

btw this isn't for hw or work, i'm just curious cuz my friend was asked this in an interview ^^

edit:

david's name would have to show up twice btw in the result

 

[Chompman]

Here you go:
Linky

 

[n0cmonkey]

http://forums.anandtech.com/ca...5813356&entercat=y

EDIT: /me adds Chompmanp to "the list."

 

[gshock888]

select (last name field) where count(last name field) > 1

 

[jinduy]

oh oh thanks!

 

[KLin]

Originally posted by: gshock888
select (last name field) where count(last name field) > 1

You're hired! 😛

 

[Hyperblaze]

does this strictly have to be done in sql?

can another language be used? (ie, php)

 

[gshock888]

do it in ASM

 

[jinduy]

has to be done in sql, my friend didn't get the job because the other dude he was competing with got a more efficient answer... so im really curious. i'm no sql expert but i think gshock got it right woot!

 

[gshock888]

Originally posted by: jinduy
say you have a list of employees with their names...

let's say there's two guys named David

how would you make a query where it only displays duplicate names, such as David's?

btw this isn't for hw or work, i'm just curious cuz my friend was asked this in an interview ^^

if its for a job u best know more than the count function... brush up on your sql skillz

 

[jinduy]

oh wait that's not right i forgot to mention that the query would have to display David twice, not just one david, which that query would do i think.


correct result:

1. David
2. David

incorrect:

1. David

 

[ISAslot]

select (field) where (field)='David';

 

[jinduy]

Originally posted by: ISAslot
select (field) where (field)='David';

well let's negotiate.

 

[gshock888]

how about this

select count(lastname) as count_name, lastname from table where count_name>1

gets you

2 david
5 smith


etc etc, you can even use order by count_name to start from lowest dupes to most dupes

 

[djheater]

I'd walk ove the Pete, the SQL guy and have him write me a .sql file that I can call from vbs or a bat...

But that's just me...

 

[KLin]

Here is how Access finds duplicates, and still allows you to display other fields

SELECT tblName.FirstName, tblName.LastName
FROM tblName
WHERE (((tblName.FirstName) In (SELECT [FirstName] FROM [tblName] As Tmp GROUP BY [FirstName] HAVING Count(*)>1 )))
ORDER BY tblName.FirstName;

It uses a query in the where clause to find duplicate values.

 

[MichaelD]

You=(posted)..->int/the wrong forum//(the_dumbass)<--you are

 

[Chompman]

Originally posted by: MichaelD
You=(posted)..->int/the wrong forum//(the_dumbass)<--you are

:laugh:

 

[jinduy]

Originally posted by: Chompman

Originally posted by: MichaelD
You=(posted)..->int/the wrong forum//(the_dumbass)<--you are

:laugh:

hee hee

 

[Gnurb]

Originally posted by: gshock888
select (last name field) where count(last name field) > 1

Actually this doesn't work- at least in MySQL. You need to use 'having' instead of 'where' when doing things like that with count. ex:

SELECT * FROM name HAVING count(name) > 1

 

[gshock888]

my bad. that was an aggregate

 

[macwinlin]

Originally posted by: jinduy
say you have a list of employees with their names...

let's say there's two guys named David

how would you make a query where it only displays duplicate names, such as David's?

btw this isn't for hw or work, i'm just curious cuz my friend was asked this in an interview ^^

edit:

david's name would have to show up twice btw in the result

This question sounds familiar... Did your friend interview with a company here in the Los Angeles area (Glendale to be more specific)?

 

[manly]

Just join the table to itself.

 

[Evadman]

My god. This thread makes me cry.

<SQL>
SELECT * from [TableOfEmployees] where [LastNameField] IN (Select [LastNameField] HAVING Count([LastNameField]) > 1)
</SQL>

There are of course other ways to do it.

 

[ActuaryTm]

SELECT * FROM users WHERE clue > 0