457,706 Members | 1,357 Online Need help? Post your question and get tips & solutions from a community of 457,706 IT Pros & Developers. It's quick & easy.

# Select a distinct row per ID, but rather complicatedly.

 P: 8 Hi, all, I have a data as follows. ID t Status 1 0 Y 1 1 Y 1 2 Y 1 3 N 1 4 N 1 5 N 1 6 N 2 0 X 2 1 X 2 2 Y 2 3 Y 2 4 Z 3 0 Y 3 1 Y 3 2 Z 3 3 Z 3 4 Z 3 5 N 3 6 N 3 7 Y 3 8 Y 4 0 Y 4 1 Y 4 2 X Assume I have the data above. I want to create a query generating its result as follows. ID Delta T 1 1 3 2 0 4 3 1 5 4 0 2. More explanation of the rule is as follows. Each ID has varying status X/Y/Z etc. and N. Basically, want to select a row from each ID. Once 'N' is observed at least once in the status field, I want to define Delta(ID)=1. Otherwise =0. In other words, I want Delta to be an indicator if there was any status 'N' in the history of the ID. Then want to let T be the first month the 'N' is observed. If no 'N' is observed, I want Delta(ID)=0. Then I want T to be the largest t observed. I cannot create exactly what I really want for myself. Please give me a help. Thank you, Joon Sep 12 '08 #1
8 Replies

 P: 37 I'm confused. Could you post a mathmatical formula of what exactly the transformation is supposed to be? Sep 12 '08 #2

 P: 8 Let Time(ID) be a set of available t's corresponding to the given ID. (e.g. Time(ID=1) = {0,1,2,3,4,5,6}.) If Status(ID, t)= "N" for some t in Time(ID), then Delta(ID) := 1 T(ID) := min{t in Time(ID) : Status(ID, t) = "N"}. Else (that is, if Status(ID, t)<> "N" for all t in Time(ID) ), Delta(ID) := 0 T(ID) := max{t in Time(ID)}. I don't know this is enough for you to understand. Thank you, Joon Sep 12 '08 #3

 P: 37 Its better. Seems to me that you are trying to calculate answers for some variables. I'd separate it into multiple queries. Call the first your "N" query. Perform your first calculation. min{t in Time(ID)} Making sure to use the "WHERE" your field for your data ="N" Then make a second query called your "Not N" query. max{t in Time(ID)}. Making sure to use the "WHERE" your field for your data <> "N" Generally speaking when I do things like this (multiple level queries from the same data), I put the input data in one table and the output data in a second table. That way I can watch it better. Then after I make sure it works I steal the SQL code the program has made for me and run it from VBA or a macro. That way I can run the queries quicker in the future and never wonder, "Did I run both of those or just the first one?" Sep 12 '08 #4

 Expert 2.5K+ P: 2,653 Hello, jooon. May be not the most elegant but a working one: qryHasN Expand|Select|Wrap|Line Numbers SELECT t.lngID, Min(t.lngT) AS T, Sgn(Sum(t.txtStatus="N")) AS blnDelta FROM t WHERE t.txtStatus="N" GROUP BY t.lngID HAVING Sgn(Sum(t.txtStatus="N"));   qryHasNoN Expand|Select|Wrap|Line Numbers SELECT t.lngID, Max(t.lngT) AS T, Sgn(Sum(t.txtStatus="N")) AS blnDelta FROM t GROUP BY t.lngID HAVING Not Sgn(Sum(t.txtStatus="N"));   qryOverall Expand|Select|Wrap|Line Numbers SELECT * FROM qryHasN UNION SELECT * FROM qryHasNoN;   Regards, Fish Sep 12 '08 #5

 P: 8 Thank you all. I will try both. (they are possibly same?) Then get back to you whether they work or not. If not, I will ask another question. Thank you again, Joon Sep 12 '08 #6 