Query SyntaxSponsored by jamf

51K

Solutions

20K

Contributors

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

Share tech news, updates, or what's on your mind.

Sign up to Post

HELP WITH SQL SERVER 2008R2 STATEMENT

I have a table called SHIPPER with a field called CUST_ORDER_ID and PACKLIST_ID  Ex: DC180001

i have a view called CT_TRACKING_INFORMATION_VIEW that has a field called TRACKING_INFO and PACKLISTS that can contain multiple PACKLIST_ID Ex: DC180001, DC180003, DC180040

I need a select statement that pulls the SHIPPER record by ORDER_ID and the TRACKING_INFO field if the PACKLIST_ID is "in" the PACKLISTS field.
This works if i hard code the LIKE '%DC180001%'
SELECT SHIPPER.CUST_ORDER_ID, shipper.PACKLIST_ID, Tracking_Information FROM SHIPPER, CT_TRACKING_INFORMATION_VIEW WHERE CUST_ORDER_ID = 'CC181430' AND (CT_TRACKING_INFORMATION_VIEW.packlist_id LIKE '%DC180001%' OR CT_TRACKING_INFORMATION_VIEW.packlist_id IS NULL)

Open in new window


but i can't figure out how to make it use the shipper.PACKLIST_ID   i.e. This does not work:
SELECT SHIPPER.CUST_ORDER_ID, shipper.PACKLIST_ID, Tracking_Information FROM SHIPPER, CT_TRACKING_INFORMATION_VIEW WHERE CUST_ORDER_ID = 'CC181430' AND (CT_TRACKING_INFORMATION_VIEW.packlist_id LIKE '%' + shipper.PACKLIST_ID +'%' OR CT_TRACKING_INFORMATION_VIEW.packlist_id IS NULL)

Open in new window


ok, got this to work somewhat:
 WHERE     (dbo.CT_TRACKING_INFORMATION_VIEW.packlist_id LIKE '%' + s.PACKLIST_ID + '%') OR
                      (dbo.CT_TRACKING_INFORMATION_VIEW.packlist_id IS NULL)

Open in new window


but it returns 2 records for each Order 1 with Null TRACKING_INFORMATION and 1 with the actual tracking information.
Note all records will have tracking information, that is why i am including IS NULL.
what i need is for the select statement to bring back only 1 record and if it has tracking information to include it.
0
Cloud Class® Course: Microsoft Exchange Server
LVL 12
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

hi,

as MS SQL has always encrypted feature, what is the diff between Network Encryption and always encrypted?
0
Hi,

I have a two queries. One is for union and the other is for recent. From the recent query i want to do totals based on Criteria. The criteria is for same "Cou","Flow","Years","PType", Sum "Values" based on Pro. Attach is a sample DB in which you can see tblPro have different ID.The order for this tblPro is ID="1" is total for ID "200,201,300" and ID="2" is total for ID="300", so that total should be on the basis of Product with Sum of Values and group by other. i have try that in qry_total , but it's not working. Any help?

Attach is a sample DB.

Thank you.
error.accdb
0
I am looking for the following Oracle query
   I have a table TB_Docs . The DocNo column has sample values ( Length is not fixed . Also the "_ " can appear anywhere )  
     123_456-23_0
      12-32_1-24567_0
       A1-23_c2_1
      G2/334/1_A

  The "_ " before the last character should be replaced by  "/"  ( For example 123_456-23_0  should be updated as 123_456-23/0 . But it should not update any other  "_ " other than the one before the last character)
0
I had this question after viewing Syntax to import multiple files with Oracle IMP.

I invoke imp by passing parfile=Import_Parfile.txt. Is it still possible in below way? please help

file=file1.dmp,file2.dmp,file3.dmp,file4.dmp
log=file_alll.log
ignore=Y
rows=Y
grants=none
fromuser=SCOTT

Open in new window

0
I build vb.net application and connect to SQL Server 2012, and my code is to print all report that user selected (Checkbox in datagridview), let say DataGrideView has 5 Rows and user select 3 row and press Click on print button and the system will print the 3 item that user selected, but my problem is when user select 3, it will print only 2, if select 2 it will print only 1, and if select only 1 row, it will not print. And here is my code.

                        For Each drow As DataGridViewRow In DataGridView1.Rows

                            If drow.Cells(0).Value = True Then
                                Dim frm As New frmViewReports
                                Dim rpt As New rptInvoiceKHDolla
                                Dim dsRptPurchase As New dsReport1
                                Dim daPurchase As SqlDataAdapter

                                Dim strSql As String
                                strSql = "select * from v_RptInvoicesB5 where InvoiceNo=" & drow.Cells(1).Value
                                daPurchase = New SqlDataAdapter(strSql, sqlcon)
                                daPurchase.Fill(dsRptPurchase, "v_RptInvoicesB5")
                                GF_ViewReport2(rpt, dsRptPurchase, "Print Invoices")
                                drow.Cells(0).Value = False
                            End If
                        Next



and here is my function to print that report.

    Public Function GF_ViewReport2(ByVal …
0
how to convert ssql syntax to C# syntax:
declare @mnt1 decimal(18,4), @mnt_r decimal(18,4), @nbr bigint  
  declare @ST_NB_DECIMAL_QTY INT  
  set @mnt1 = 16.60
    select @ST_NB_DECIMAL_QTY = 2
        set @nbr = POWER(10 ,@ST_NB_DECIMAL_QTY)  
  Set @mnt_r = round(@nbr * @mnt1,@nbr)  
  IF @mnt_r < 0  
  Set @mnt_r = CEILING(@mnt_r)/ @nbr  
  ELSE  
  Set @mnt_r = FLOOR(@mnt_r)/ @nbr  
  Select  @mnt_r
0
Hello experts. I inherited a front end ms access application which connects to a sql database. One of my forms it connects to a sql table called tbl_conversations. The table has no primary or foreign keys (if that matters). Here is my issue. I added a new column to the table (tbl_conversations) called track. I then re-open ms access and when I attempt to update the table in the linked table manager I received a message that says "The search key was not found in any record". I select ok and naturally I cannot see the field I want to add as a record source on the form. I tried a compact and repair and still no luck. Any ideas on how to resolve would be great.
0
I clicked on the "Reports" tab of Server 2016 Windows Server Updates Services and was told that "The  "Microsoft Report Viewer 2012 Redistributable"  is required for this feature. Please close the console before installing this package."

So I clicked on the URL link and was directed to the website https://www.microsoft.com/en-us/download/details.aspx?id=35747 where I downloaded the "Microsoft Report Viewer 2012 runtime."

However, when I installed the "Microsoft Report Viewer 2012 runtime" ReportViewer.msi file I received a message that said "Setup is missing an installation prerequisite. -Microsoft System CLR Types for SQL Server 2012" (see the screenshot).

I have googled this and the results I have found have had me download several different MSI installation files without telling me exactly which one(s) I need to install. I want to make sure that I only install the component or program that I absolutely need and that I don't install anything extra.

So what is the best URL to visit to download and install the right "Microsoft System CLR Types for SQL Server 2012?"

Microsoft System CLR Types
0
sql and php vulnerabilities
0
Keep up with what's happening at Experts Exchange!
LVL 12
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

using System.Collections.Generic;
using System.ComponentModel;

using System.Drawing;
using System.Text;
using System.IO;
using System.IO.Ports;
using System.Threading;
using System.Text.RegularExpressions;
using System;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void SqlStoredProcedure1(string strPortName, string strBaudRate, String strPhone, String StrMsg)
    {
        try
        {
            bool sms_status;
        SerialPort port = new SerialPort();
        port.PortName = strPortName;
        port.BaudRate = Convert.ToInt32(strBaudRate);               //updated by Anila (9600)
        port.DataBits = 8;
        port.StopBits = StopBits.One;
        port.Parity = Parity.None;
        port.ReadTimeout = 300;
        port.WriteTimeout = 300;
        port.Encoding = Encoding.GetEncoding("iso-8859-1");
        //  port.DataReceived += new SerialDataReceivedEventHandler(port_DataReceived);
        port.Open();
        port.DtrEnable = true;
        port.RtsEnable = true;
        StoredProcedures p = new StoredProcedures();
        sms_status = p.sendMsg(port, strPortName, strBaudRate, strPhone, StrMsg);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public AutoResetEvent receiveNow;



    //Close Port
    public void ClosePort(SerialPort port)
    {
  

Open in new window

0
Hi

Is it possible to have a line break in an ASP.net GridView cell that is bound to SQL data?
0
Hello Experts:

It's been a while since I used "case statements" in MS-Access.

I need some assistance with using a wildcard (*) in a case statement.   Below is what I have:

Public Function Q8_If_Married_Convert(Q8_If_Married As Variant) As Variant

        Select Case Q8_If_Married
        
           Case "Resides with spouse":                                                                   Q8_If_Married_Convert = "Resides with spouse"
           Case "Separated":                                                                                   Q8_If_Married_Convert = "Separated"
           Case "Separated due to other reasons (e...g... deployed)":                    Q8_If_Married_Convert = "Separated due to other reasons"
           Case Else:                                                                                               Q8_If_Married_Convert = "No data available"
           
        End Select

End Function

Open in new window



In this example, the problem lies occurs for the 3rd statement.   Rather than having a hard-coded case equal to "Separated due to other reasons (e...g... deployed):", I prefer using the case "Separated due to other reasons*" (ending with the wild card).   The key challenge how the data entry person entered the reference to "deployment".   That is, in this case, too many periods were used in the "e.g." reference.   Alternatively, in other cases, the separation reason may not be "deployment" but could be "XYZ" reason.

Can anyone offer some assistance with a) recommendation how to tweak the case select statement or b) offering an alternative that allows me to convert all possible outcomes for the "Separated due to other reasons*" scenario?

Thank you,
EEH
0
Hi All,

We are developing a application primarily using web api, entity framework, angular js, sql server and C# and hosted on dedicated server running single web site on IIS
Once we have done the data migration (loaded about 150K records)  we have noticed application performance was deistically reduced.
Eg. Service call takes about 15 -20 second (GET) and post is about 20 – 30 seconds.
To overcome this issue I thought of converting some of the codes into stored procedures.

Apart from doing so what we can do in IIS to gain performance boost.
0
Hi,

In my LAB I have installed the MBAM server and SQL DB's on a cluster. Reporting services also installed...
I can access the MBAM web page without problems.

GPO's configured as well.

However, after noticing that I can't find any recovery info of my test devices I wanted to check directly in the database.

There are no tables in the DB at all.

I then dropped the Recovery DB and created it again.
No errors whatsoever, but the DB still doesn't contain any tables.

Also not in the Event Viewer. It all looks fine. But DB remains empty.

Any ideas?

Screenshot:


0
Hi,

I've been working on improving this Query and I've been able to reduce a lot of Logical Reads through Indexing.  But I've been wondering if there's any way to improve the Query's OR conditional.


/****** Object:  StoredProcedure [dbo].[std_pro1]    Script Date: 6/19/2018 1:46:33 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE  procedure [dbo].std_pro1  
     @FLG_BUSQUEDA_AVANZ        smallint
    ,@FECH_DOC_TDOC_INI_TXT     varchar(10)
    ,@FECH_DOC_TDOC_FIN_TXT     varchar(10)
    ,@FECH_CREA_TDOC_INI_TXT    varchar(10)
    ,@FECH_CREA_TDOC_FIN_TXT    varchar(10)
    ,@FECH_ENV_TDOC_INI_TXT     varchar(10)
    ,@FECH_ENV_TDOC_FIN_TXT     varchar(10)
    ,@CODI_EDOC_TED             smallint
    ,@CODI_TDOC_TTD             numeric(9)
    ,@CODI_TMOV_TTM             numeric(9)
    ,@NRO_REG_TDOC              int
    ,@NUM_DOC_TDOC              varchar(40)
    ,@CODI_EATD_TED             numeric(9)
    ,@FLG_ANU_TDOC              char(1)
    ,@NOM_EMI_TSEG              varchar(250)
    ,@EMP_EMI_TSEG              varchar(100)
    ,@OFI_EMI_TSEG              int
    ,@CODI_EMI_TSEG             int
    ,@NOM_DEST_TSEG             varchar(250)
    ,@EMP_DEST_TSEG             varchar(100)
    ,@OFI_DEST_TSEG             int
    ,@CODI_DEST_TSEG            int
    ,@ASUN_DOC_TDOC             varchar(2000)
    ,@USU_EMI_TSEG              int
    ,@ENC_EMI_TSEG              int
    ,@USU_DEST_TSEG             int
    ,@ENC_DEST_TSEG             

Open in new window

0
I am calling a table valued function which is returning multiple columns in select subquery, yet I know that select query returns single column at a time only.
So how is it possible to do

subquery
     SELECT TOP (1) CRFCA.Name, CRFCA.Rate, CRFCA.ChemicalFamily, CRFCA.WHP, CRFCA.qty, CRFCA.totalMixer
            From fnChemicalRecordForChemicalApplication(@companyID) as CRFCA           
            WHERE .ChemicalApplicationID = chemicalApplication.ChemicalApplicationID
            ) AS value8

Open in new window


Function
   create function fnChemicalRecordForChemicalApplication(@companyID int)
returns table
as
return (
		SELECT  chemicalApplicationChemical.ChemicalApplicationID as ChemicalApplicationID, chemical.Name as Name, chemicalApplicationChemical.Rate as Rate,                                               chemical.ChemicalType as ChemicalFamily, chemical.Whp as WHP, chemicalApplicationChemical.Quantity as qty, chemicalApplicationChemical.TotalMixture as totalMixer
                FROM ChemicalApplicationChemical AS chemicalApplicationChemical              
                INNER JOIN Chemical AS chemical ON chemical.ChemicalID = chemicalApplicationChemical.ChemicalID
				WHERE  (chemical.CompanyID = @companyID)
)

Open in new window

0
I have an Oracle database with Table TB_Documents   I need to get all the Docuemnts ending as _0
While I try  DocNo like   '%_0'  , I am getting more rows than the ones I need . The _ seems to be ignored
How can I run a query to get the right result ?
0
Should you be able to backup sql express database and import into sql 2016? I go through the process and the back up takes about 5 min and the restore takes seconds. The backup file is 4 gb. Before I execute the command I select contents and it is empty.
0
Free Tool: Path Explorer
LVL 12
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Hello, I need to mix 2 queries.
How can i do it?

1.

SELECT * FROM (
SELECT * FROM (
SELECT '0' OUTCHK, CLSMON, ZCODEP, ZCOROT, BIZMON, ZCOVVD, A.VVDSEQ, A.SVCTYP, STAPOT, STADAT, DIGITS(DEC(STATIM,4,0)) STATIM, STAKND
       , ENDPOT, ENDDAT, DIGITS(DEC(ENDTIM,4,0)) ENDTIM, ENDKND, LLPPOT, LLPDAT, LLPKND, OPRDAY, PRODAY
       , CASE WHEN TRIM(NEXVVD) = 'PO' THEN 'Phase-out'
              WHEN NEXVVD LIKE '_______Z' THEN 'B075참조'
              WHEN C.LTMOPR = 'HMM' THEN '자사운용선박'
              ELSE '슬로티지선박' END AS RMK, NEXVVD
       , CASE WHEN COALESCE(LLPKND,' ')||COALESCE(STAKND,' ')||COALESCE(ENDKND,' ') IN ('CCA','ALL','ALC','ALA','ACL','ACC','ACA') THEN 'Y' ELSE 'N' END ERRYN
       , PROPER , VVDSTA, MVPWKY AS MVPWKY
       , ROWNUMBER() OVER (PARTITION BY MVPTYP, MVPVVD, MVPTR2, MVPROT, ZCOVVD ORDER BY MVPWKY DESC) AS ROWNUM
       , '' AS INTAG
       , A.CFMTAG, ZCOBON
       , CASE WHEN STADAT IS NULL OR STADAT = '' THEN '1'
              WHEN ENDDAT IS NULL OR ENDDAT = '' THEN '1'
              WHEN LLPDAT IS NULL OR ENDDAT = '' THEN '1'
              WHEN ENDDAT < STADAT THEN '1'
              ELSE '0' END AS CHKVLD
       , 'C' AS OUTDIV
       , 'test' AS OUTTEST
  FROM PLIBCO.ZCORESVVDT A LEFT OUTER JOIN PLIBOP.MRKAVSLP B
        ON MVPTYP = 'B' and MVPLOD = 'L' and MVPKND = 'H/Q' and MVPROT = ZCOROT and MVPVVD = ZCOVVD
        and MVPTR2 = CASE WHEN ZCODEP = 'P1060' THEN 'UT'
                          WHEN ZCODEP = 'P1061' THEN…
0
In general, when you run an aggregate query in Access...how would you select the Min or Max of the results?
For example, I have the following sql
Lets try this again...I've changed the SQL slightly
SELECT DLPReport.[Employee - Key] AS EmpID, DLPReport.[Cost Center], Sum(DLPReport.[Total Comp]) AS [SumOfTotal Comp]
FROM DLPReport
GROUP BY DLPReport.[Employee - Key], DLPReport.[Cost Center]
HAVING (((DLPReport.[Employee - Key])=846843));

Open in new window

This gives me three rows of data in the results:
EmpID      Cost Center      SumOfTotal Comp
800003      4001              3,190.53
800003      4002               5,146.86
800003      4003                      -5.28

How would I return only the MAX of the [SumofTotal Comp], which is the middle record with 5,146.86?
0
I have this bit of sql code. Can anyone tell me what the "VALUE_DATE_OFFSET_NO,-20" and "VALUE_DATE_OFFSET_NO,20" gives me?
No comments in the code. What is this trting to acheive?
Is it saying to subtract 20 from the column and add 20 to the column for the declared variables?

DECLARE @MinCapacityOffset INT
DECLARE @MaxCapacityOffset INT


-- Look up the minimum and maximum offsets specified in the Capacity standards table.
SELECT            @MinCapacityOffset = MIN(ISNULL(TCAPACITY.VALUE_DATE_OFFSET_NO,-20)),
            @MaxCapacityOffset = MAX(ISNULL(TCAPACITY.VALUE_DATE_OFFSET_NO,20))
FROM            TCAPACITY
0
Hello expert,

In previous question titled 'DECLARE CURSOR IS SELECT BEGIN'
The Cursor/Loop form was used to display a list of names and Contact IDs

Informative but not too useful.

In this question, more complex, there are attached texts to create three
tables
TMP_SOURCE_180615 has fields Contact_ID, FirstName and LastName

TMP_SOURCE_180617 has fields Contact_ID, Contact_Phone, FirstName and LastName

TMP_SOURCE_180618 has fields Contact_ID, LastName, and Contact_Phone

PS File names too long for display. Have truncated the names to the last six digits..

Ususally a script is attempted before asking a question because I don't
believe in just asking a completely open ended question like 'How do I do this?'

But have tried a couple of things and it is obvious that I am not even close.

However within the context of the script marked 'Declare Cursor TMP_SOURCE_180615'
copied herewith below what is the approach and tactic:

Where the Contact_ID is a certain value in TMP_SOURCE_180615
Fetch the CONTACT_PHONE that has that CONTACT_ID value from TMP_SOURCE_180617
and insert the CONTACT_PHONE value that matches the Contact_ID in TMP_SOURCE_180618
in the matching CONTACT_Phone field.

Again would like to make question more specific but am in uncharted waters and cannot
find a tutorial that addresses this issue.

Will double point on answer.

Allen in Dallas

---- Declare Cursor TMP_SOURCE_180615 ------
SET SERVEROUTPUT ON
DECLARE
      CURSOR …
0
I cannot get past a sql error of missing keyword on this sql.   I have ** the section that is giving me trouble.   When I take out the second else statement I get a divisor by zero error, so I don't think it's that.  

Select V_RT_CYCLE_PART_COUNTS.EQNO,
  V_RT_CYCLE_PART_COUNTS.ITEMNO,
  V_RT_CYCLE_PART_COUNTS.WORKORDER_ID,
  V_RT_CYCLE_PART_COUNTS.STDCAV,
  V_RT_CYCLE_PART_COUNTS.ACTCAV,
  case when V_RT_CYCLE_PART_COUNTS.STDCAV  is null or V_RT_CYCLE_PART_COUNTS.STDCAV = 0 then 0
else round((100-( V_RT_CYCLE_PART_COUNTS.ACTCAV /  V_RT_CYCLE_PART_COUNTS.STDCAV)*100),0)
end "% BLOCKED",
  ROUND(V_RT_CYCLE_PART_COUNTS.SHIFT_UP,4) AS "SHIFT UP",
  ROUND(V_RT_CYCLE_PART_COUNTS.SHIFT_DWN,4) AS "SHIFT DOWN",
 
  case when V_RT_CYCLE_PART_COUNTS.SHIFT_UP is null or V_RT_CYCLE_PART_COUNTS.SHIFT_UP = 0 then 0
else ROUND(V_RT_CYCLE_PART_COUNTS.SHIFT_UP/(V_RT_CYCLE_PART_COUNTS.SHIFT_UP + V_RT_CYCLE_PART_COUNTS.SHIFT_DWN)*100,2)
end "AVAILABILITY",

 ROUND (3600/V_RT_CYCLE_PART_COUNTS.STD_CYCLE,2) AS "BOM CPH",
 ( ROUND (3600/V_RT_CYCLE_PART_COUNTS.STD_CYCLE,2) * ROUND(V_RT_CYCLE_PART_COUNTS.SHIFT_UP))AS "EXPECTED CPS",
 V_RT_CYCLE_PART_COUNTS.CYCLES_SHFT,

****
case when V_RT_CYCLE_PART_COUNTS.CYCLES_SHFT is null or V_RT_CYCLE_PART_COUNTS.CYCLES_SHFT = 0 then 0
else V_RT_CYCLE_PART_COUNTS.SHIFT_UP is null or V_RT_CYCLE_PART_COUNTS.SHIFT_UP = 0 then 0
else ROUND(V_RT_CYCLE_PART_COUNTS.CYCLES_SHFT / ((3600/V_RT_CYCLE_PART_COUNTS.STD_CYCLE) * …
0
I've got two MSSQL (2014) tables:
  create table PARENT( ID int );
  create table CHILD( PARENTID int, CHILDID int, AGE int )
/* there are 3 parents */
  insert into PARENT values( 1 );
  insert into PARENT values( 2 );
  insert into PARENT values( 3 );
/* there are 4 children to parent 1, aged 5,6,7,8 */
  insert into CHILD values( 1, 10, 5 );
  insert into CHILD values( 1, 11, 6 );
  insert into CHILD values( 1, 12, 7 );
  insert into CHILD values( 1, 13, 8 );
/* there are 2 children to parent 2, aged 15,16 */
  insert into CHILD values( 2, 20, 15 );
  insert into CHILD values( 2, 21, 16 );
/* there are no children to parent 3 */

Open in new window


I can read out all parents and their children (and the children's age) with:
  select p.ID, c.CHILDID, c.AGE
    from PARENT p
    left join CHILD c on c.PARENTID=p.ID
  order by p.ID, c.AGE desc

Open in new window


Now I however would like to get a list with one row only per parent, including the ID and AGE of the three oldest children in separate columns.
If there are less than three children for a parent, the corresponding ID and AGE columns for the child should be null.

Any ideas to do this?

I may get such a list for only parents with at least three children doing like this - but that is not good enough :-(
select
  p.ID, 
  c1.CHILDID, c1.AGE,
  c2.CHILDID, c2.AGE,
  c3.CHILDID, c3.AGE
from PARENT p
cross apply (select ROW_NUMBER() over (order by AGE desc) as ROWNO, * from CHILD where PARENTID=p.ID) c1
cross apply (select ROW_NUMBER() over (order by AGE desc) as ROWNO, * from CHILD where PARENTID=p.ID) c2
cross apply (select ROW_NUMBER() over (order by AGE desc) as ROWNO, * from CHILD where PARENTID=p.ID) c3
where 
  c1.ROWNO=1 and c2.ROWNO=2 and c3.ROWNO=3
order by p.ID

Open in new window


Thanks,
/Stefan
0

Query SyntaxSponsored by jamf

51K

Solutions

20K

Contributors

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.