Trouble with combining columns

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
cobweb34
Forum Newbie
Posts: 10
Joined: Wed Jan 27, 2010 5:50 am

Trouble with combining columns

Post 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
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: Trouble with combining columns

Post by papa »

Code: Select all

SELECT ID, CALL, INDEX FROM table ORDER BY INDEX DESC
cobweb34
Forum Newbie
Posts: 10
Joined: Wed Jan 27, 2010 5:50 am

Re: Trouble with combining columns

Post 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?
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: Trouble with combining columns

Post 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
cobweb34
Forum Newbie
Posts: 10
Joined: Wed Jan 27, 2010 5:50 am

Re: Trouble with combining columns

Post 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?
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: Trouble with combining columns

Post by papa »

Code: Select all

SELECT ID, CALL, INDEX FROM table ORDER BY INDEX DESC LIMIT 1
cobweb34
Forum Newbie
Posts: 10
Joined: Wed Jan 27, 2010 5:50 am

Re: Trouble with combining columns

Post 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?
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: Trouble with combining columns

Post by papa »

You get an error?
cobweb34
Forum Newbie
Posts: 10
Joined: Wed Jan 27, 2010 5:50 am

Re: Trouble with combining columns

Post by cobweb34 »

Yeah:

Incorrect syntax near 'LIMIT'
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Trouble with combining columns

Post 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;
cobweb34
Forum Newbie
Posts: 10
Joined: Wed Jan 27, 2010 5:50 am

Re: Trouble with combining columns

Post by cobweb34 »

ahh it's working now. Thanks Papa and Mikosiko..I appriciate all the help, cheers. :D
Post Reply