Subquery Help I Think - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Subquery Help I Think

Author

Topic

StephanieJones
Starting Member

12 Posts

Posted - 2004-11-23 : 13:53:16

Can someone please help me with this. I am not very good with subqueries but I do recognize when I need one. Let me try to describe what I need. I have a table that contains several rows that are related to each other by a field - OriRecordID field and then there is an "instance" field that contains a sequential numerical value. When I make the first record on "Issue 123" the "IssueInstance" field populates with a "1". The next time I update that issue, a new record is inserted into the database with the same OriRecordID field - "Issue 123" and the "IssueInstance" field is now "2". On each entry into the database, I have a primary key field that is autonumbered. I want to be able to pull up all the pieces of the each record (contains 8 fields) but only the Max IssueInstance for each OriRecordID. Can someone help me?

Stephanie Jones

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-23 : 14:01:02

It would be easier if you spoke in "code"

We would need to see the Table DDL

Like

CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 char(10), ect

What some sample data would look like..

Like

INSERT INTO myTable99(Col2, ect)
SELECT 'x','ect' UNION ALL
SELECT 'x','ect' UNION ALL
...

And what the expected results are suppose to look like

1 x ect
2 y ect
3 z ect

Off the cuff...sounds like you need to group by some columns and use MIN or MAX or some scalar function

Brett

8-)

themirage
Starting Member

4 Posts

Posted - 2006-06-25 : 02:44:47

Hi,
I kinda have the same problem!

Background:
Let's say I have a table:
Date_f | Field1
---------------
1Aug | A
2Jan | H
7Oct | G

Problem Statemnet:
I want (in one go) to retrive the value of "Field1" related to max(Date).
How can I do this?

More on background:
It's mid 2006 and we're still stuck with Ora 8i
Ofcourse the query I want is not that hard, actually it's not hard at all:
SELECT subquery.maximum_date
, t1.field1
FROM table1 t1
, (SELECT MAX (date_f) AS maximum_date
FROM table1) subquery
WHERE t1.date_f = subquery.maximum_date

So what's the problem?
Problem is, I kinda have to evaluate this query-subquery system for eachrecord in an even bigger query because of a restiction on the date field that I have to put under a HAVING cluase!

Your help is highly appreciated :-)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-26 : 02:16:34

quote:
Originally posted by X002548

It would be easier if you spoke in "code"

We would need to see the Table DDL

Like

CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 char(10), ect

What some sample data would look like..

Like

INSERT INTO myTable99(Col2, ect)
SELECT 'x','ect' UNION ALL
SELECT 'x','ect' UNION ALL
...

And what the expected results are suppose to look like

1 x ect
2 y ect
3 z ect

Off the cuff...sounds like you need to group by some columns and use MIN or MAX or some scalar function

Brett

8-)

What happened to your signature?

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-26 : 02:18:33

What is the datatype of Date_f?
If you used DateTime datatype to stored dates, then it would be easy

Select Field1, Max(date) as Dates from yourTable
Group by Field1

Madhivanan

Failing to plan is Planning to fail

themirage
Starting Member

4 Posts

Posted - 2006-06-26 : 02:47:25

I just meant date field by "Date_f", so yes, it is a date.
But if you Select Field1, Max(date) as Dates from myTable
it won't work unless I GROUP BY Field1, so I gained nothing!!!

Mirage
\m/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-26 : 04:22:08

Yes it is. You have to use Group by clause. Otherwise post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

themirage
Starting Member

4 Posts

Posted - 2006-06-26 : 05:07:09

Okay,
Back to my sample table:
Date_f | Field1
---------------
1Aug | A
2Jan | H
7Oct | G

I want to select the maximum date, which in this case is '7Oct', and at the same time select the value associated with this max(date_f), which is in the this case 'G'.
If grouped by field1 I would get the same table again! Right?
So instead of select max (date_f) then select field1 where date_f = (result from first query) I want to have this in a single shot! Not even a single query that has a subquery.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-26 : 05:25:54

Select Field1, Max(datecol) as Dates from yourTable
Where datename(month,DateCol)='October' Group by Field1

Madhivanan

Failing to plan is Planning to fail

themirage
Starting Member

4 Posts

Posted - 2006-06-26 : 08:02:07

quote:
Originally posted by madhivanan

Select Field1, Max(datecol) as Dates from yourTable
Where datename(month,DateCol)='October' Group by Field1

Madhivanan

Failing to plan is Planning to fail

OH MY GOD!
Dude, how would I know it's October without looking in the table first?! October here is just an example, it changes the dates change every day! I have about 240 new records every day, most of them will have different dates than TODAY!

Again: I wan't to select a maximum of a column and get the value of another column but on the same row of this maximum value that I just got WITHOUT having to subquery.

Mirage

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources