.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Kaviya Balasubramanian
Imran Ghani
Post New Web Links

order by in combination with union

Posted By:      Posted Date: August 24, 2010    Points: 0   Category :ASP.Net
 

 Hi All,

How can I order this by count? so if it appears above and below the union statement, I want it on top

	SELECT distinct BIER.Naam, BIER_L2.L2_Naam, BIER_L3.L3_Naam from BIER_MATCH, BIER, BIER_L2, BIER_L3
	where BIER_L2.L2_ID = BIER.L2_ID and BIER_L2.L3_ID = BIER_L3.L3_ID and BIER.ID = BIER_MATCH.BIER_ID 
	and RECEPT_ID = @RECEPT_ID 

	
	UNION  
	
	--L2
	SELECT distinct BIER.Naam, BIER_L2.L2_Naam, BIER_L3.L3_Naam from BIER_MATCH, BIER, BIER_L2, BIER_L3
	where BIER_L2.L2_ID = BIER.L2_ID and BIER_L2.L3_ID = BIER_L3.L3_ID and RECEPT_ID = @RECEPT_ID 
	and BIER_MATCH.BIER_ID is null


 




View Complete Post


More Related Resource Links

Multiple Order by clauses with Union

  

I'm trying to figure out how to run this code, I have over 40 tables that I need to get data from, the only thing I can think of is to use "Union" , I need to get the last record in each table by StartTS (StartTimeStamp) so I figured the best approach is to use "Select Top (1) and use Order by StartTS to get the data.  This works great if you are only grabbing data from 1 table, however like I said I need to grab from over 40. 

My example uses only 2 tables:

SELECT   TOP (1)
             (SELECT   [Unit Name]
              FROM     dbo.UnitVerificationData
              WHERE   (Unit_Code = dbo.CP_BCELP.Unit_Code)) AS UnitName, Operation_type, StartTS, StopTS
FROM     dbo.CP_BCELP
ORDER BY StartTS DESC
UNION
SELECT   TOP (1)
             (SELECT   [Unit Name]
              FROM     dbo.UnitVer

order by sort both select in union

  

Hi

I have DropDownList that Show all groups. in that, I want to show "select one group" as default in DDL. for that i used code below. but it sort all return data and "select one group" will not be the first line. How can i fix it?

Best Regards. Morteza

 

	SELECT   -1 as id,'select one group' as groupName
	union
	SELECT   id, groupName
	FROM     [group] 
	order by groupNam

Using UNION with ORDER BY

  

It is interesting but when I try to execute query with union word and ORDER BY like that:

Select EmpId, OrderDate, OrderId
From [TestSQL].[Sales].[Orders]
INTERSECT 
SELECT TOP 2 EmpId, OrderDate, OrderId From [TestSQL].[Sales].[Orders] WHERE EmpId=3
ORDER BY OrderDate DESC
UNION
Select EmpId, OrderDate, OrderId
From [TestSQL].[Sales].[Orders]
INTERSECT 
SELECT TOP 2 EmpId, OrderDate, OrderId From [TestSQL].[Sales].[Orders] WHERE EmpId=5
ORDER BY OrderDate DESC
I get the mistake:

Incorrect syntax near the keyword 'UNION'.

But when I use derived table like that :

Using Union and Order in query

  

SQL Server 2008. Guys,  I need to do following. I have tables Employees(empid,country,region,city) and Suppliers(supplyid,country,region,city). So I have a query:

SELECT country, region, city
FROM HR.Employees

UNION ALL

SELECT country, region, city
FROM Production.Suppliers;

So I need

to add logic to the query 
such that it would guarantee that the rows from Employees
would be returned in the output before the rows from Customers,
and within each segment, the rows sh

Sorting ORDER BY - WORD

  

Is it possible with SQL sorted in descending order according to the word?
In Table I shrajeno Mastercard, Visa, Diners etc ...

All Visa are the first in the order, another Mastercard, etc. ...

Something does not work: ORDER BY PaymentType = 'Visa';

I would not want to work in one field PaymentId (111,222,33, etc. ..) and then use the ORDER BY.

Is this possible in such a way?


Linq qry with order by basic questions

  

I have never tried a group by with linq, now I do and I'm stuck.

I try to convert this sql statement to its linq counterpart :

SELECT COUNT(tb_Mailer_Smses.Id) AS [cnt], tb_Mailer_mailing.Id As [Id]
,SUM( CASE WHEN tb_Mailer_smses.DLRStatus = 200 THEN 1 ELSE 0 END) As [Delivered]
,SUM(CASE WHEN DLRStatus BETWEEN 300 AND 499 THEN 1 WHEN DLRStatus BETWEEN 800 AND 899 THEN 1 END) AS [Errors]
FROM tb_Mailer_mailing INNER JOIN
tb_Mailer_Smses ON tb_Mailer_mailing.Id = tb_Mailer_Smses.MailingId
WHERE tb_Mailer_mailing.ApplicationId = 1
GROUP BY tb_Mailer_mailing.Id


Is there some one who can me show me the convertion process ?


Need Syntax To Make Results of LINQ Union Return Non-Generic Type

  

Hi.

I have the below SQL which works just fine:

SELECT     Message, CreateDate, AccountId, AlertTypeId
FROM       dbo.Alerts
UNION
SELECT     TOP (100) PERCENT Status, CreateDate, AccountId,
                          (SELECT     10 AS Expr1) AS AlertTypeId
FROM         dbo.StatusUpdates
WHERE AccountId = PassedInParameter
ORDER BY CreateDate DESC

I have been going about trying to convert the above into a LINQ method and am having problems with return types.
As the above SQL illustrates, I am querying two distinct LINQ tables - Alerts and StatusUpdates -
and then attempting to perform a Union on them. In order to do the union in LINQ, I have to have agreeing types. So, I
am using "var" to make the results of "alerts" and "updates" generic before the union.  

public IList GetStatusUpdatesAsAlertsByAccountId<T>(Int32 accountId)
        {
            using (WorkbookDataContext dc = _conn.GetCont

ASP.NET: Collect Customer Order Information on an Internet Site Using XML and Web Forms

  

XML has quickly become the new data structure standard for everything from database access to cross-platform computing. XML is typically considered to be a vehicle for data exchange, dynamic data presentation, and data storage. However, the potential of XML far surpasses those limited applications. This article examines one new use: the gathering of data across a number of forms in an ASP.NET Beta 1 framework application. The sample program is a Web app used for ordering pizza. It uses ASP and C# to gather order information and then stores it in XML. To build the application, several concepts are explained, including data collection, order persistence using cookies, grouping input forms, and formatting the data for display.

Jeff Jorczak

MSDN Magazine September 2001


formula to display all combination output

  

hi all

i am now think of how to get all the output for all the combinations

eg.

given : 1234

output = 1234,1243,2143......and so on.....all 24 possible output

if given : 123

output = all 12 output

if given 12345 , all 120 output...

can someone provide me the algorithm or formula for that ?

thanks


Is it possible to filter KPI list in order to display only relevant KPIs

  

I  have created a KPI list and I want to show sections of the list on various parts of my site, however, I dont seem to be able to filter the list. Has anyone tried this and had any success?


How to union two queries without duplicates

  


I have a sql query that returns 4 columns
CustName CustId CustZip CustPhone

I have a second sql query that returns the following 5 columns

CustName CustId CustZip CustEmail CustAddress

Both queries, query different data tables in the database, but return columns that are common to
both.

How do I union the two queries(Assuming a union is needed)

Which will result in no duplicates and an end result being the following output:

CustName CustId CustZip CustPhone CustEmail CustAddress

 

As you can see we want to not have duplicate values on output. So something like the following
is not acceptable:

Jeff Stamper  2222234  81224  498-300-2222
Jeff Stamper  2222234  81224  498-300-2222 js@jj.com 122 Mars Blvd
Karen Bops  3322234    81666  498-300-2222
Karen Bops  3322234    81666  498-300-2222 kb@lpo.com 322 Jamer Road


insert into in combination with inner join

  

Hi All,

I have my data split in 3 tables for cascading dropdownlists.

How can I make an update statement of this select statement?

SELECT distinct BIER.Naam as naam1, BIER_L2.L2_Naam as naam2, BIER_L3.L3_Naam as naam3
	from BIER, BIER_L2, BIER_L3
	where BIER_L2.L2_ID = BIER.L2_ID and BIER_L2.L3_ID = BIER_L3.L3_ID 
 
Thanks


 

 


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend