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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Other Forums
 SQL Server 6.5 \ SQL Server 7.0
 minimum value required in a column for each row.
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

koolkaus
Starting Member

India
9 Posts

Posted - 03/19/2015 :  12:27:42  Show Profile  Reply with Quote
Need help with a quick one. I need to create a query to get a value against each row suggesting whether the AppID was uploaded first time (Upload_D). this should result in 1 = firt time, 0 = more than first time.

Current Structure of Table:

ToDotype UID AppID Upload_D End_D_T

ToDotype = type of work (5 categories)
UID= unique number.
AppID= a non unique text
Upload_D = an integer or date suggesting time when an entry was created in DB.
END_D_T = time when a user striked it off from their list.



Rgds,
Kaus

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 03/19/2015 :  12:31:29  Show Profile  Visit tkizer's Homepage  Reply with Quote
It's important to clarify which SQL Server version you are using since you posted in the SQL Server 6.5/7.0 forum.

Also, please post sample data and expected result set.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22864 Posts

Posted - 03/19/2015 :  12:45:04  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Start with this approach

select t1.*, case when t2.upload_D is null then 0 else 1 end as first_time from table as t1
LEFT JOIN
(
select AppId,min(upload_D) as upload_D from table group by AppId
) as t2
on t1.AppId=t2.AppId and t1.upload_D=t2.upload_D


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22859 Posts

Posted - 03/19/2015 :  13:04:18  Show Profile  Reply with Quote
Do you just want to have a column in your query result to indicate if Upload_D is the minimum value for a given AppID ?

SELECT AppID,
       Upload_D,
       CASE WHEN EXISTS (SELECT * FROM MyTable AS T2 WHERE T2.AppID = T1.AppID AND T2.Upload_D < T1.Upload_D)
            THEN 0 ELSE 1 END AS IsFirst
FROM MyTable AS T1

Edited by - Kristen on 03/19/2015 13:04:58
Go to Top of Page

Kristen
Test

United Kingdom
22859 Posts

Posted - 03/19/2015 :  13:06:05  Show Profile  Reply with Quote
Madhi's code is much the same

@Madhi: I wonder if the JOIN to a sub-query with GROUP BY is more efficient than an EXIST in the SELECT clause?
Go to Top of Page

koolkaus
Starting Member

India
9 Posts

Posted - 03/19/2015 :  14:59:56  Show Profile  Reply with Quote
Thank you folks. I will try these suggestions.

Rgds,
Kaus
Go to Top of Page

koolkaus
Starting Member

India
9 Posts

Posted - 03/20/2015 :  00:59:41  Show Profile  Reply with Quote
My apologies first of all as this is related to MS Access 2010.
I am trying to shoot an SQL query through Excel.

It seems that the Case When statement is not supported by Access 2010 and an alternate is Switch. I tried using the above from Kristen however no success...

If I write it with Switch then is it correct to say:

SELECT AppID,
Upload_D,
SWITCH (SELECT * FROM MyTable AS T2 WHERE T2.AppID = T1.AppID AND T2.Upload_D < T1.Upload_D, 1,SELECT * FROM MyTable AS T2 WHERE T2.AppID = T1.AppID AND T2.Upload_D >= T1.Upload_D,0 ) AS IsFirst
FROM MyTable AS T1
?


Rgds,
Kaus
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22864 Posts

Posted - 03/20/2015 :  02:34:07  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by koolkaus

My apologies first of all as this is related to MS Access 2010.
I am trying to shoot an SQL query through Excel.

It seems that the Case When statement is not supported by Access 2010 and an alternate is Switch. I tried using the above from Kristen however no success...

If I write it with Switch then is it correct to say:

SELECT AppID,
Upload_D,
SWITCH (SELECT * FROM MyTable AS T2 WHERE T2.AppID = T1.AppID AND T2.Upload_D < T1.Upload_D, 1,SELECT * FROM MyTable AS T2 WHERE T2.AppID = T1.AppID AND T2.Upload_D >= T1.Upload_D,0 ) AS IsFirst
FROM MyTable AS T1
?


Rgds,
Kaus


Can you use IIF?

select t1.*, IIF(t2.upload_D is null,0,1) as first_time from table as t1
LEFT JOIN
(
select AppId,min(upload_D) as upload_D from table group by AppId
) as t2
on t1.AppId=t2.AppId and t1.upload_D=t2.upload_D



Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22864 Posts

Posted - 03/20/2015 :  02:36:54  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by Kristen

Madhi's code is much the same

@Madhi: I wonder if the JOIN to a sub-query with GROUP BY is more efficient than an EXIST in the SELECT clause?


Yes I think so. Because the EXISTS has be executed for each and every row joining back to the same table. Not sure if it matters if you use different small table.

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22859 Posts

Posted - 03/20/2015 :  03:46:35  Show Profile  Reply with Quote
Does the JOIN's Sub Query GROUP BY have to process the whole table, and then selectively JOIN, or is it smart enough to be correlated?

I could do a test but hopefully you know the answer off the top of your head?
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22864 Posts

Posted - 03/20/2015 :  05:00:28  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by Kristen

Does the JOIN's Sub Query GROUP BY have to process the whole table, and then selectively JOIN, or is it smart enough to be correlated?

I could do a test but hopefully you know the answer off the top of your head?


In my test, my version uses hash join which is internally doing co-relation. But I think TEST must have tested this already

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22859 Posts

Posted - 03/20/2015 :  06:30:16  Show Profile  Reply with Quote
Yeah, but TEST is old so already forgotten what the result was
Go to Top of Page

koolkaus
Starting Member

India
9 Posts

Posted - 03/20/2015 :  06:54:19  Show Profile  Reply with Quote
Thank you again. I am trying the query suggested at Posted - 03/20/2015 : 02:34:07 by Madhivanan.
Using a small table is not an alternate. This is an output from an existing "legacy" project. Cannot change DBs as software depends on it which is too big to change..

Rgds,
Kaus
Go to Top of Page

koolkaus
Starting Member

India
9 Posts

Posted - 03/20/2015 :  07:14:44  Show Profile  Reply with Quote
Got a message that reads:
Circular reference caused by alias 'Upload_DT' in query definition's list.

I narrowed down the query to only
select AppId,min(upload_D) as upload_D from table group by AppId

& still received the same message. is this because we are using upload_D twice?

Also, while I was reading the query (please consider I am bit too novice in writing these SQLs), I was not sure if it is written to provide the below:
1) Same number of Rows
2) 1 Additional Columns that provides 0 or 1, lets say: First_Time
FActor to consider:
Upload_D = when an entry is keyed into the system. the idea is to know first_time when an app_id was uploaded.

Life is beautiful cause of helpful people like you all. Thank You for all the efforts.

Rgds,
Kaus
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22864 Posts

Posted - 03/20/2015 :  07:28:14  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Do not use * in SELECT Statement. Explicitely type out the columns needed

select t1.AppId, IIF(t2.upload_D is null,0,1) as first_time from table as t1
LEFT JOIN
(
select AppId,min(upload_D) as upload_D from table group by AppId
) as t2
on t1.AppId=t2.AppId and t1.upload_D=t2.upload_D


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22864 Posts

Posted - 03/20/2015 :  07:30:49  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by Kristen

Yeah, but TEST is old so already forgotten what the result was


Good to see you again after a long time

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

huangchen
Starting Member

USA
37 Posts

Posted - 04/02/2015 :  05:53:29  Show Profile  Reply with Quote
unspammed
Go to Top of Page

newwaysys
Starting Member

India
9 Posts

Posted - 04/13/2015 :  05:57:16  Show Profile  Reply with Quote
It seems that the Case When statement is not supported by Access 2010 and an alternate is Switch. I tried using the above from Kristen however no success...

If I write it with Switch then is it correct to say:

SELECT AppID,
Upload_D,
SWITCH (SELECT * FROM MyTable AS T2 WHERE T2.AppID = T1.AppID AND T2.Upload_D < T1.Upload_D, 1,SELECT * FROM MyTable AS T2 WHERE T2.AppID = T1.AppID AND T2.Upload_D >= T1.Upload_D,0 ) AS IsFirst
FROM MyTable AS T1
?
ecently I generate Code 39 barcode in Reporting Service with this barcode tool unspammed
Because the EXISTS has be executed for each and every row joining back to the same table. Not sure if it matters if you use different small table.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000