Home » SQL & PL/SQL » SQL & PL/SQL » Return just one value
Return just one value [message #219023] Mon, 12 February 2007 10:50 Go to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
I'm trying to determine how to return just the rows I want from this table.

Problem: I need to return just one curriculum_abbr and it's corresponding name (curriculum_name) even if there are multiple curriculum_abbr with the same abbr.

Any ideas on how to do this?

Data:

curriculum_abbr                   curriculum_name
---------------                   ---------------
ACCORD                             ACCORDION
ACCTNG                             ACCOUNTING
AD JUS                             ADMINISTRATION OF JUSTICE
AD JUS                             PACE-ADMINISTRATION OF JUSTICE
AM ST                              AMERICAN STUDIES
AM ST                              PACE-AMERICAN STUDIES
ART                                ART
ART                                ART - VIDEO NETWORK
ART                                PACE - ART
CV ENG                             CIVIL ENGINEERING
CV ENG                             CIVIL ENGINEERING-GRADUATE
CV ENG                             CIVIL ENGINEERING - VIDEO NETWORK

Output should be:

curriculum_abbr                   curriculum_name
---------------                   ---------------
ACCORD                             ACCORDION
ACCTNG                             ACCOUNTING
AD JUS                             ADMINISTRATION OF JUSTICE
AM ST                              AMERICAN STUDIES
ART                                ART
CV ENG                             CIVIL ENGINEERING



As you can see, I need to return just one curriculum_abbr with it's curriculum_name.
Re: Return just one value [message #219030 is a reply to message #219023] Mon, 12 February 2007 11:05 Go to previous messageGo to next message
pselvam76
Messages: 12
Registered: February 2007
Junior Member
Hi

Is it ok , if we can give any curriculam name or we should get only those mentioned by you.

If yes use the following query

Select curriculum_abbr, max(curriculum_name)
from <table_name> group by curriculum_abbr;

or

Select curriculum_abbr, min(curriculum_name)
from <table_name> group by curriculum_abbr;

Thanks
Panneer Selvam
Re: Return just one value [message #219034 is a reply to message #219023] Mon, 12 February 2007 11:20 Go to previous messageGo to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
Ideally I would rather have the first row listed for each abbr but hey, I'll take anything I can get.

So, to answer you question, I would rather have "ADMINISTRATION OF JUSTICE" instead of "PACE-ADMINISTRATION OF JUSTICE" or "CIVIL ENGINEERING" instead of "CIVIL ENGINEERING-GRADUATE", "CIVIL ENGINEERING - VIDEO NETWORK".

For now, all I care about is returning one row of abbr and name.
Re: Return just one value [message #219036 is a reply to message #219023] Mon, 12 February 2007 11:36 Go to previous messageGo to next message
pselvam76
Messages: 12
Registered: February 2007
Junior Member
In that case you can use any of the above queries listed

Thanks
Panneer Selvam
Re: Return just one value [message #219038 is a reply to message #219023] Mon, 12 February 2007 11:40 Go to previous messageGo to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
Perfect, thanks.

Just out of curiosity, is there a way to do it with the way I have my output listed?

It looks like using the min() value will work but you made it sound like there might be a different way if I needed the output as I have them listed.
Re: Return just one value [message #219040 is a reply to message #219023] Mon, 12 February 2007 11:57 Go to previous messageGo to next message
pselvam76
Messages: 12
Registered: February 2007
Junior Member
Hi,

Yes we can get this using min. But I don't know you always want this way. For example if you have a data

Test Test
Test ABC Test

And if you need the result as

Test Test

Then it won't work. In that case it is better to add a new column sort_seq_no to the table & you need to define the seq in which it should appear. In that case we can consider the min or max value based on your requirement.

Thanks
Panneer Selvam
Re: Return just one value [message #219041 is a reply to message #219023] Mon, 12 February 2007 12:07 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Once again, the ole "relational database" comes into play here. There is no such thing as "the first", or "just one" value unless you specifically use an order by clause. You are going to get a random returned value, which in your case doesn't seem to matter. But behold, you run the script next week and you are liable to get different results.


I would like to know however, what is the logic behind getting just one row when there are actually more than one name for the abbreviation. To me, this is the same as having an abbreviation of LA meaning both Los Angeles and Louisiana. If you pick just one, how is that correct?
Re: Return just one value [message #219042 is a reply to message #219041] Mon, 12 February 2007 12:21 Go to previous messageGo to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
I'm not sure how to respond to your questions. Let's just say that's the "University" way of doing it. That's about all I can say about that. Hey, I just work here and didn't come up with this stuff.
Re: Return just one value [message #219113 is a reply to message #219042] Tue, 13 February 2007 00:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The 'University way' is to question what you do and not respond with 'hey, I just work here'...
Re: Return just one value [message #219180 is a reply to message #219023] Tue, 13 February 2007 09:09 Go to previous messageGo to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
Ok, that wasn't the greatest response. My point being, I didn't design the tables and there is no way I can change them. I just work with what I've been given, whether good or bad. The tables are what they are and there is nothing I can do about them, period.

A rough analogy would be, I inherited a car from my Dad who bought the car new from X auto maker with no ability to open the hood (everything is one piece). This was the way the car was designed and produced by the engineers at the X auto maker. Since it's an older car, the intake gasket is leaking and has to be replaced. So, it's my job to somehow replace the leaking intake gasket with no way of opening the hood.
Re: Return just one value [message #219205 is a reply to message #219180] Tue, 13 February 2007 10:01 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I did not mean to offend you personally, what I meant is that too many people seem to take a design as a fixed thing. Too many people think they HAVE to build it the way a designer/analist thought it was best.

Back to your problem.
You must have a way to decide which of the records prevails, why it is that you want that one and not one of the others.
Once you can tell what makes you decide which one to take, you might translate that to sql.
Re: Return just one value [message #219210 is a reply to message #219205] Tue, 13 February 2007 10:16 Go to previous message
Duane
Messages: 452
Registered: December 2002
Senior Member
Oh, I understood that. I just wanted you to understand where I was coming from and what I had to work with.

I'll see what I can come up and do what you suggest.
Previous Topic: status of Oracle service automatically sets to null
Next Topic: error:service not avail
Goto Forum:
  


Current Time: Tue Dec 06 08:53:07 CST 2016

Total time taken to generate the page: 0.10856 seconds