Trouble with combining columns
Moderator: General Moderators
Trouble with combining columns
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
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
Code: Select all
SELECT ID, CALL, INDEX FROM table ORDER BY INDEX DESCRe: Trouble with combining columns
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?
Is there a way of me being more precise so only returning the maximum values?
Re: Trouble with combining columns
Is max 3 ?
Top 5
Code: Select all
SELECT ID, CALL, INDEX FROM table WHERE INDEX = 3Code: Select all
SELECT ID, CALL, INDEX FROM table ORDER BY INDEX DESC LIMIT 5Re: Trouble with combining columns
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?
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
Code: Select all
SELECT ID, CALL, INDEX FROM table ORDER BY INDEX DESC LIMIT 1Re: Trouble with combining columns
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?
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
You get an error?
Re: Trouble with combining columns
Yeah:
Incorrect syntax near 'LIMIT'
Incorrect syntax near 'LIMIT'
Re: Trouble with combining columns
This is what you wantcobweb34 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?
Code: Select all
SELECT a.call, max(a.index) from table a
group by a.call;Re: Trouble with combining columns
ahh it's working now. Thanks Papa and Mikosiko..I appriciate all the help, cheers. 