Some SQL help please.

McGintyMcGoo

I have been asked to create an index for a publication. I have been given a flat table that contains "pub_name", "town", "county" and "page_no". They want an index created to show the lowest page number that a particular town appears on.

For example, if the sample data read as follows ...
Joe's Pub - Athlone - WestMeath - 22
Moe's Pub - Athlone - WestMeath - 23
Roe's Pub - Athlone - WestMeath - 24

... then the index at the back should have one entry for Athlone
Athlone - WestMeath - 22

Using a "SELECT DISTINCT" on a combination of town and county will give me a distinct list of towns, but when I include the page number in that query it just gives me all of the entries because the page number is different each time.

Any idea on how to do this? I assuming that it probably a sub-query that selects a minimum page number but I can't get it to work.

Cheers

JackieChan

Easy:

select town,county,min(index number)
from table
group by town,county

JC

McGintyMcGoo

Ahh the "Group By" statement. The most basic of basic SQL statments and I forgot all about it.

Thanks very much for the help JackieChan.

clearz

Accually I have a SQL problem also which this thread made me think of (Well its not really a problem but its something ive been thinking about). I have a table full of estate-agent properties houses/appartments ect. In this table there is a field Property_of_the_month which are all zero's apart from the current property of the month which has a 1 in it. On the front-end GUI there is a checkbox to represent this. I am looking for a SQL statement that will remove the 1 in whatever property it is in and then insert it into the new property when the checkbox is clicked. I am currently using two lines of sql to do this. An update statement to set them all to zero and then an insert statement to set the new property to 1. Is there some fancy way of doing this using only an insert statement.

PhilH

I think the answer depends on how fancy you want to get.

The first thing to notice is that you want to set the values of two rows, not one.

The *simple* and probably best way to do it is with the two statements you mentioned. Anything else is probably too complex a solution to a simple problem.

I can think of a least two fancy ways which let you do it in less calls to the server, but only the most moronic pedant would think that this was more efficient...

1) Put a trigger on the table, so that setting a value from 0 to 1 causes all the other values to go to 0.

2) Try some torturous SQL like

update places set pom = 
	case when id=4 then 1
	else 0 end;

This will set pom to 0 on all rows except the one with id=4.

PHiL

clearz

Thanks that works. Its not to do with efficiency. I just like learning new more elegant ways of doing things.

PhilH

Its not to do with efficiency. I just like learning new more elegant ways of doing things.

Ermm... I don't think you want to consider this 'elegant'. Its obscure, non-intuituve, takes a while to figure out what's going on, will confuse a lot of people, and its a well-hidden performance problem waiting to happen. It is not quiet as bad as the triggers idea, but it is definitely not good.

I would argue that the elegant solution is the two-statement solution that you mentioned earlier...

update places set pom=0
update places set pom=1 where id=4

Or preferably (if you know the id of the current property of the month)...

update places set pom=0 where id=3
update places set pom=1 where id=4

This is all the things that the fancy one-statement that I posted isn't. It is obvious what it is going on. A lightly trained monkey can understand it. It is fast. You can't ask much more from your code than that.

PHiL