SQL question

Quote from nitro:

I am not sure I follow. Are you saying that it makes no sense to show duplicate rows? In the example I gave before, it makes perfect sense. Say you are storing news items in a database. Say your columns are

GUID(PK), DateTime, Headline, NewsStory, Author.

Say you want to return all the news stories and the author for a single day by a given author. Clearly this is a table that is a bunch of duplicate entries.
More...

my understanding of the original 3 column table was different...

but in the case above why not SELECT * FROM @table WHERE Author = @author AND Date = @date?

my wonder is why do you want to see 2 exactly the same rows shown twice (instead of - at maximum - been notified of number of occurrences of the same row?)
 
Quote from dhpar:

my understanding of the original 3 column table was different...

but in the case above why not SELECT * FROM @table WHERE Author = @author AND Date = @date?

my wonder is why do you want to see 2 exactly the same rows shown twice (instead of - at maximum - been notified of number of occurrences of the same row?)
More...
What if you don't know what date it could happen? You just want any date where two things happened together. What if you think that anytime two stories by (any) two different people comes out on a given day, there is collusion, or there is some correlation, or ....

The point is you want to see duplicates, then see if there is causation in the duplicate, or maybe you want to do some operation on the duplicates, whatever...
 
Quote from nitro:

What if you don't know what date it could happen? You just want any date where two things happened together. What if you think that anytime two stories by (any) two different people comes out on a given day, there is collusion, or there is some correlation, or ....
More...

well i clearly do not understand your problem. if there are 2 different people then you do not have identical rows and so on...

i guess it is important that you solved your problem (whatever it was). just keep in mind that designing a proper database (indexing, data types, keys, relationship, triggers) adds a lot of speed and saves you a lot of coding and errors.
 
ok now i see it.

so it is not about rows but rather that you want to see all news articles if and only if a specific author posted more than 1 article in a given time period...from the original post that was not obvious. but then the solution could be much easier then the one above. e.g. select * from TBL where author in (select author from TBL where date=@date group by author having count(author)>1)

cheers
 
Quote from dhpar:

ok now i see it.

so it is not about rows but rather that you want to see all news articles if and only if a specific author posted more than 1 article in a given time period...from the original post that was not obvious.

cheers
More...
Not quite. I want to see _any_ two entries where they matched on date (without the time part), but I want to select whatever (rest of the) columns I want when displaying the table.

In fact, I don't want entries by the _same_ person at all. I want to exclude those (Say an author was only allowed to enter one news story per day, but it could be any day). I want to see who else wrote on that day, and I want to see all the columns for the return table.
 
Nitro..
group by the key that shows duplicates, counting the number of entries that are the same. put the results of the select INTO a second temporary table and select on the count

say you have people with duplicate names..

SELECT name, count(*) 'countx'
INTO #mylist
FROM listofnames
GROUP BY name

--show all entries with duplicates
SELECT name
FROM #mylist
WHERE countx > 1

--show all entries without duplicates
SELECT name
FROM #mylist
WHERE countx = 1

--throw away the temporary table
DROP TABLE #mylist
 
patoo,

Thank you for your code. But here is the problem I am trying to solve: I have a table with columns x,y,z. x can contain many duplicates (say the date without the time part). I want a list of all the x duplicate rows, and their corresponding y and z columns in a table.

Your code only allows me to see the column that is duplicated I believe, something I could achieve as posted in the original code in this thread.

Quote from patoo:

Nitro..
group by the key that shows duplicates, counting the number of entries that are the same. put the results of the select INTO a second temporary table and select on the count

say you have people with duplicate names..

SELECT name, count(*) 'countx'
INTO #mylist
FROM listofnames
GROUP BY name

--show all entries with duplicates
SELECT name
FROM #mylist
WHERE countx > 1

--show all entries without duplicates
SELECT name
FROM #mylist
WHERE countx = 1

--throw away the temporary table
DROP TABLE #mylist
More...
 
Quote from nitro:

patoo,

Thank you for your code. But here is the problem I am trying to solve: I have a table with columns x,y,z. x can contain many duplicates (say the date without the time part). I want a list of all the x duplicate rows, and their corresponding y and z columns in a table.

Your code only allows me to see the column that is duplicated I believe, something I could achieve as posted in the original code in this thread.
More...

If I understand your question correctly, this is a common problem.

You just have to join the query selecting the duplicates with another query that's selecting the columns you want from the same table e.g.

<pre>
SELECT x, y, z
FROM table
INNER JOIN
(SELECT x
FROM table
GROUP BY x
HAVING count(*) > 1) AS duplicates
ON table.primary_key = duplicates.primary_key
</pre>

...or something like that.

There are a couple of possibilities depending on which database you are using (I missed if you mentioned it).
 
Right,

This is just restating the top two posts on page two.

Quote from byteme:

If I understand your question correctly, this is a common problem.

You just have to join the query selecting the duplicates with another query that's selecting the columns you want from the same table e.g.

<pre>
SELECT x, y, z
FROM table
INNER JOIN
(SELECT x
FROM table
GROUP BY x
HAVING count(*) > 1) AS duplicates
ON table.primary_key = duplicates.primary_key
</pre>

...or something like that.

There are a couple of possibilities depending on which database you are using (I missed if you mentioned it).
More...
 
You must log in or register to reply here.
Back
Top