Page 1 of 1

Trouble with combining columns

Posted: Thu Mar 18, 2010 8:35 am
by cobweb34
ID - CALL - INDEX
1       1         1
2       2         1
3       2         2
4       1         2
5       1         3
6       3         1

OK, I have a database that looks like the above and I want to display the highest INDEX per CALL.

For example, if I was to get results from the above table, they would look like this:

CALL - INDEX
1         3
2         2
3         1

If anyone knows how to do this I will be eternally grateful if you could explain it to me.

Thanks

Re: Trouble with combining columns

Posted: Thu Mar 18, 2010 8:56 am
by papa

Code: Select all

SELECT ID, CALL, INDEX FROM table ORDER BY INDEX DESC

Re: Trouble with combining columns

Posted: Thu Mar 18, 2010 9:03 am
by cobweb34
Thanks for your answer Papa, I never thought of doing it that way, cheers.

Is there a way of me being more precise so only returning the maximum values?

Re: Trouble with combining columns

Posted: Thu Mar 18, 2010 9:06 am
by papa
Is max 3 ?

Code: Select all

SELECT ID, CALL, INDEX FROM table WHERE INDEX = 3
Top 5

Code: Select all

SELECT ID, CALL, INDEX FROM table ORDER BY INDEX DESC LIMIT 5

Re: Trouble with combining columns

Posted: Thu Mar 18, 2010 9:13 am
by cobweb34
Ahh, that's the problem, there is no limit.

The database is for a Helpdesk Call Logging system. Currently there are 193217 CALLs in the database, and the INDEX is the amount of updates so can range between 3 and 300. So if the index peaks at 45, I'd like to display the number 45.

any ideas?

Re: Trouble with combining columns

Posted: Thu Mar 18, 2010 9:14 am
by papa

Code: Select all

SELECT ID, CALL, INDEX FROM table ORDER BY INDEX DESC LIMIT 1

Re: Trouble with combining columns

Posted: Thu Mar 18, 2010 9:34 am
by cobweb34
Papa,

I've entered exactly what you've said but it doesn't work with the "LIMIT 1" on the end.

Apart from that bit, everything works fine. I'm using MS SQL Server, would that make a difference?

Re: Trouble with combining columns

Posted: Thu Mar 18, 2010 9:49 am
by papa
You get an error?

Re: Trouble with combining columns

Posted: Thu Mar 18, 2010 9:52 am
by cobweb34
Yeah:

Incorrect syntax near 'LIMIT'

Re: Trouble with combining columns

Posted: Thu Mar 18, 2010 9:56 am
by mikosiko
cobweb34 wrote:Papa,

I've entered exactly what you've said but it doesn't work with the "LIMIT 1" on the end.

Apart from that bit, everything works fine. I'm using MS SQL Server, would that make a difference?
This is what you want

Code: Select all

SELECT a.call, max(a.index) from table a
group by a.call;

Re: Trouble with combining columns

Posted: Thu Mar 18, 2010 10:12 am
by cobweb34
ahh it's working now. Thanks Papa and Mikosiko..I appriciate all the help, cheers. :D