Need Help Writing A Sql

[mcmuney]

I'm not sure where to begin on this one. I'm working with a comment table and trying to determine who is writing the most "first" comment. For example, the table would have the followin fields:

 

id, comment, to_id, from_id

 

So what I need is to determine which 'from_id' is writing the most very first comment.

 

For example:

 

id, comment, to_id, from_id

1,abc,5,6 (6 is first)

2,bcd,5,7

3,lkd,9,7 (7 is first)

4,ljf,9,6

5,lsj,4,6 (6 is first)

6,lds,4,5 (5 is first)

 

In this example, from_id 6 would have a count of 2; 7 count of 1, 5 count of 1.

 

How would I write this statement?

[scootstah]

A definitive answer depends on your database structure. For example, how are you determining what the "first" comment is?

 

You would likely use a COUNT() and group by the user ID. But, again, it depends on your database structure and how the "first" comment is determined.

[mcmuney]

There's no "first" indicator on the DB. It has to be determined by the 'id' i'm guessing? For example:

 

3,lkd,9,7 (7 is first)

4,ljf,9,6

 

If this is grouped by to_id and somehow pull the count based on the lowest id, is that possible?

 

I also have a date field if that's of any help.

[Jessica]

Don't you think the date would be useful to determine first?

[mcmuney]

I could be wrong, but it seems that both the 'date' and 'id' would serve the same purpose in determining the first. You can take the lowest id for each to_id or the earliest date.

 

Don't you think the date would be useful to determine first?

[Barand]

SELECT a.comment, a.to_id, a.from_id as first
FROM comments a
INNER JOIN
   (
   SELECT to_id, MIN(id)as minid
   FROM comments
   GROUP BY to_id
   )
   as b ON a.to_id = b.to_id AND a.id = b.minid

[Lyleyboy]

You could select distinct and limit 1 ordered by the id.

[scootstah]

3,lkd,9,7 (7 is first)

4,ljf,9,6

 

Where is the logic here? Why is 7 first?

[DavidAM]

I could be wrong, but it seems that both the 'date' and 'id' would serve the same purpose in determining the first. You can take the lowest id for each to_id or the earliest date.

 

The 'id' is never a good choice in determining the "first" post. In the beginning, yes, the AUTO INCREMENT field will be, well, auto incrementing; and lower id's will come before higher id's. But this is an arbitrary situation. If you ever have to reoganize the table or re-sequence the id's or delete older entries (with lower id's) and re-start the numbering; then the lower id will not be "first". You must first define what "first" means. If the definition of "first" is "earliest in time", then a DATETIME field should be used.

[Barand]

Where is the logic here? Why is 7 first?

 

First reply where to_id = 9

 

See my reply #6

Edited September 25, 2012 by Barand

[scootstah]

First reply where to_id = 9

 

Ah okay, so using the AUTO_INCREMENT column on the comment. Eh, I think using a date would be better.

[Barand]

The 'id' is never a good choice in determining the "first" post. In the beginning, yes, the AUTO INCREMENT field will be, well, auto incrementing; and lower id's will come before higher id's. But this is an arbitrary situation. If you ever have to reoganize the table or re-sequence the id's or delete older entries (with lower id's) and re-start the numbering; then the lower id will not be "first". You must first define what "first" means. If the definition of "first" is "earliest in time", then a DATETIME field should be used.

Ah okay, so using the AUTO_INCREMENT column on the comment. Eh, I think using a date would be better.

 

I agree on the date comments, but the id is all the OP would give us to work with