Update all but last two rows of each category

Posted: 29 Jul 2013 01:01 AM PDT

I would like to update the complete flag of all the rows of a table except the most recent two rows of each category.The table has fields like: category - string, item - string, creation_date - string ('YYYYMMDDHHMI'), complete_flag - boolean, etc.Each category does not have the same amount of records with the same creation_date so I do not want to filter by creation_date.Is there a way to accomplish this?Thanks for your help,Fred

CAN ANY ONE WRITE QUERY FOR BELOW DESCRIPTION!

Posted: 14 Jan 2010 03:20 AM PST

345 0 Test Lane 0 0, Binder, CA 00002 (MLS ID: PD1LST32)6 N Training Road 0, Binder, CA 00006 (MLS ID: TRN1LST38)1 N Testing Qa 0, Binder, CA 00001 (MLS ID: QA1LST267)1 N Testing Qa 0, Binder, CA 00001 (MLS ID: QA1LST618)1 N Testing Qa 0, Binder, CA 00001 (MLS ID: QA1LST660)300 WILLIAMSON STREET, CELINA, TN 38551 (MLS ID: 125833)4005 LIVINGSTON HWY., CELINA, TN 38551 (MLS ID: 127371)1097 HORSE CREEK ROAD, CELINA, TN 38551 (MLS ID: 122846)1022 JIMMY RENEAU ROAD, CELINA, TN 38551 (MLS ID: 506612)4440 Neeley Creek Rd, Celina, TN 38551 (MLS ID: 865001)130 Lake View Drive, Celina, TN 38551 (MLS ID: 129079)1022 JIMMY RENEAU ROAD, CELINA, TN 38551 (MLS ID: 123625)387 Morgan Hill St, Simi Valley, CA 93065 (MLS ID: 70001200)3140 Griffon Ct, Simi Valley, CA 93065 (MLS ID: F1703822)4877 Canoga Ave, Woodland Hills, CA 91364 (MLS ID: F1708906)21801 SAN MIGUEL ST, Woodland Hills, CA 91364 (MLS ID: FR2056685)4412 Canoga Ave, Woodland Hills, CA 91364 (MLS ID: F1709916)4222 Canoga Dr, Woodland Hills, CA 91364 (MLS ID: F1710050)4877 Canoga Avenue, Woodland Hills, CA 91364 (MLS ID: 70003126)22300 AVENUE SAN LUIS, Woodland Hills, CA 91364 (MLS ID: FR2063889)3404 MANDEVILLE CANYON RD, LOS ANGELES, CA 90049 (MLS ID: 06-150775)21635 MEDINA ESTATES DR, Woodland Hills, CA 91364 (MLS ID: FR2043630)1 N Testing Qa 123, Binder, CA 00001 - $13,99829500 HEATHERCLIFF RD Unit: 289, Malibu, CA1 N Testing Qa 123, Binder, CA 00001 (MLS ID: QA1LST282)4935 DROUBAY DR, Las Vegas, NV 89122 (MLS ID: 748023)Newbury Park, CA - $638,9001401 Calle De Oro, Thousand Oaks, CA - $645,000Newbury Park, CA - $648,000856 Masterson Dr, Thousand Oaks, CA - $650,0001841 Pinedale Ave, Lincoln, NE 68506 - $202,5001594 Norman Ave, Thousand Oaks, CA - $650,0007600 Ringneck Dr, Lincoln, NE 68506 - $214,500Greenwich, CT 06830 (MLS ID: 69294)Aspen, CO 81611 (MLS ID: 102392)1818 SE 10Th St, Fort Lauderdale, FL 33316 (MLS ID: F751628)Palm Beach, FL 33480 (MLS ID: 07-1241)

Select variable based upon sub query

Posted: 28 Jul 2013 08:34 PM PDT

I have the following SQL statement (It's actually MYSQL but the principal is the same ;-) ) that selects all the values from a table based upon the row EditionID passed to it via $POST - in this example EditionID is 10The query gives me 2 values as a tolerance + and - 10 on which to base the list from using BETWEENHowever, I want the @tol variable to change to a larger tolerance (lets say 20) if Value1 is >= the integer specified in the variable @trig. I'm not sure how to go about this - I'm guessing that the Value1 values need to be retrieved via the sub queries before making another pass at the sub queries, this time with the correct @tol variable.Unless somehow I can set 2 variables and the query chooses the correct one?This is what I have got so farSET @tol = 10;SELECT EditionID, Make, Model, EditionDesc, Value1 FROM t1 HAVING Value1 BETWEEN (SELECT Value1, TRUNCATE(Value1-@tol,1) as Value1min FROM t1 WHERE EditionID = 10) iTable1) AND (SELECT Value1, TRUNCATE(Value1+@tol,1) as Value1max FROM t1 WHERE EditionID = 10) iTable2) ORDER BY Value1Edit: Apologies errors at my end have duplicated the Post

select or count based on condition

Posted: 29 Jul 2013 12:18 AM PDT

[font="Courier New"]I have a query that returns results similar to this:Name-Order-Item - ItemShipDateTimeJoe - O23 - I11 - 2013-07-29 13:05:00Jim - O24 - I11 - 2013-07-29 13:07:00Jim - O24 - I12 - 2013-07-29 13:07:00Sue - O25 - I21 - 2013-07-29 14:05:00Sue - O25 - I44 - 2013-07-29 15:55:00Actually, I'm not interested in the Item. I just want to count when an order is complete, and get counts per hour. The result set above represents three complete orders. Discarding "Item," my unique results would be:Name-Order-ItemShipDateTimeJoe - O23 - 2013-07-29 13:05:00Jim - O24 - 2013-07-29 13:07:00Sue - O25 - 2013-07-29 14:05:00Sue - O25 - 2013-07-29 15:55:00So, I'm getting closer to three. How would I SELECT (or COUNT) only rows that contain the MAX Value in the column ItemShipDateTime field where NAME and ORDER are identical?[/font]Thanks!

Handle error record from User Defined Function

Posted: 28 Jul 2013 10:44 PM PDT

Hi,Please let me know if we can handle error record from User defined function.SQL script enclosed with this mail.regards,Kumar.

Change sub query variable based upon sub query

Posted: 28 Jul 2013 08:27 PM PDT

I have the following SQL statement (It's actually MYSQL but the principal is the same ;-) ) that selects all the values from a table based upon the row EditionID passed to it via $POST - in this example EditionID is 10The query gives me 2 values as a tolerance + and - 10 on which to base the list from using BETWEENHowever, I want the @tol variable to change to a larger tolerance (lets say 20) if Value1 is >= the integer specified in the variable @trig. I'm not sure how to go about this - I'm guessing that the Value1 values need to be retrieved via the sub queries before making another pass at the sub queries, this time with the correct @tol variable. SET @tol = 10;SET @trig = 300;SELECT EditionID, Make, Model, EditionDesc, Value1 FROM t1 HAVING Value1 BETWEEN (SELECT Value1, TRUNCATE(Value1-@tol) as Value1min FROM t1 WHERE EditionID = 10) iTable1) AND (SELECT Value1, TRUNCATE(Value1+@tol) as Value1max FROM t1 WHERE EditionID = 10) iTable2) ORDER BY Value1

Need T-SQL Query Help- Urgent

Posted: 28 Jul 2013 06:11 PM PDT

I have a table Called Employee_AttendaneIts result Is:Employee_id IS_Al Attendance1998 1 2013-03-181998 1 2013-03-191998 1 2013-03-212830 1 2013-03-042830 1 2013-03-052830 1 2013-03-06Here, for employee_id=1998, its IS_AL=1 for Date '2013-03-18','2013-03-19', '2013-03-21'And My required Result IS:Employee_id From_date To_date1998 2013-03-18 2013-03-191998 2013-03-21 2013-03-212830 2013-03-04 2013-03-06 please help with T-SQL QUERY.

Detecting ALTER or CREATE PROCEDURE??

Posted: 28 Jul 2013 12:38 PM PDT

Can't get this working - whinges about ALTER/CREATE statement syntax ?? Help?IF (SELECT count(name) FROM sys.sysobjects WHERE (type = 'P') and name = 'SPNAME') > 0BEGIN ALTER PROCEDURE [spname] ----ENDELSE BEGIN CREATE PROCEDURE [spname] ----ENDAS <body of the sp>

need urgent help for sum aggregation

Posted: 28 Jul 2013 06:07 AM PDT

can some give me hint i am trying to get a statement that will perform a daily sum aggregation on the field cost. It must only use costs that are associated with the follow items: Z001, Z002. The cost must also only come from division this what I have tried :DECLARE @dailycost dateSELECT @dailycost = GETDATE()if @dailycost = @dailycostSELECT SUM(Cost) FROM dbo.Costwhere Item = 'ZOO1'

need help with pivot

Posted: 28 Jul 2013 10:39 AM PDT

I need to write a query without using pivot. Here is the scenarioneed to display summition of data yearly and year should be display on columns.example- InputID Amount Year1 50 19811 200 19812 300 19823 70 19852 500 1981output ID 1981 1982 1983 19851 250 2 500 300 3 70 Please help..

daily sum aggretation

Posted: 28 Jul 2013 05:31 AM PDT

Sql samples, sql tips

Monday, July 29, 2013

[T-SQL] Update all but last two rows of each category

[T-SQL] Update all but last two rows of each category

No comments:

Post a Comment

Search This Blog