• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Tell me this Logic

 
Dhiraj Srivastava
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can any one tell me logic behind the following:

I want to display only those numbers which has duplicate values

Table Structure:

No
1
1
2
3
4
4

I want result should be

1
4

Please help me out from this.

Thanks in advance

[Bear edit: removed urgent from title]
[ April 21, 2005: Message edited by: Bear Bibeault ]
 
Srinivasa Raghavan
Ranch Hand
Posts: 1228
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can display this using subquery & a in clause.

PS :- Using words like "Urgent" is not good here . Please change your subject.
 
Dhiraj Srivastava
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can u please give me code.. Sorry I m not good in SQL
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 65342
97
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry I m not good in SQL


Then it is in your best interest to ask your questions in a way that makes people want to help you.

A few tips:

Don't use "urgent" in your title or message. It's rude and pushy and makes people want to ignore you. I have done you the favor of removing it from your title.

Don't use abbreviations such as "u" instead of "you". If you're not going to take the time to clearly write out the question, why should anyone take the time to answer you?

For more suggestions read these tips.
[ April 21, 2005: Message edited by: Bear Bibeault ]
 
Gregg Bolinger
Ranch Hand
Posts: 15304
6
Chrome IntelliJ IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Here is a good tutorial on SQL. You'll want to use the DISTINCT keyword when pulling the data. For example...

SELECT DISTINCT [column] FROM [table]
 
Michael Matola
whippersnapper
Ranch Hand
Posts: 1826
5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Here are two of the most useful SQL queries you will ever encounter:

select <field>
from <table>
group by <field>
having count(*) > 1

select *
from <table>
where <field> in ( select <field>
from <table>
group by <field>
having count(*) > 1 )

The first one (substitute appropriate field and table names) gives what you're asking for.

If you want to know how many times the value is duplicated you can make the select list

select <field> , count(*)
...

The second one (which nests the first one) gives all rows for a duplicate value (the duplicate being defined by what field(s) you're equating between the main query and subquery).

(You didn't ask about the second case, but it's a handy thing to know.)

Read up on aggregating (grouping) functions in SQL.
[ April 21, 2005: Message edited by: Michael Matola ]
 
Michael Matola
whippersnapper
Ranch Hand
Posts: 1826
5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
GB: You'll want to use the DISTINCT keyword

I can't think of any way to do this sort of thing using distinct. Willing to share yours?
 
Dhiraj Srivastava
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Michael !!!

Its working perfectly...
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic