Shortcut for ‘Select Top 1000 Rows’

There has to be a better way!

When writing a subquery code I need all the rows listed from a table so I can manipulate what I want grouped/summed/etc. The only way I have found to do this is to find that table in the SQL Object Explorer, right click, and hit ‘Select Top 1000 Rows’, then copying and pasting into my subquery.

Isn’t there a shortcut or something so I don’t have to get out of my query to do that? Why can’t I select the table and right click or something.

Please someone tell me there is a better way!!

How to SELECT DISTINCT records with INNER JOIN?

I have two tables Finance Commissions May2021 (FMAY) and Consolidated Client Codes (CCC).

Here is the code for Insert Into: For table FMAY

 USE [FinanceCommissions26May2021] GO  /****** Object:  Table [dbo].[Finance_Commissions_May26_2021$  ]    Script Date: 22/06/2021 11:06:26 AM ******/ SET ANSI_NULLS ON GO  SET QUOTED_IDENTIFIER ON GO  CREATE TABLE [dbo].[Finance_Commissions_May26_2021$  ](     [Lender Reference] [nvarchar](255) NULL,     [F2] [nvarchar](255) NULL,     [F3] [nvarchar](255) NULL,     [Client] [nvarchar](255) NULL,     [F5] [nvarchar](255) NULL,     [Supplier] [nvarchar](255) NULL,     [Product] [nvarchar](255) NULL,     [Settlement / Inception Date] [nvarchar](255) NULL,     [Base Value] [nvarchar](255) NULL,     [Original Loan Amount] [money] NULL,     [Adviser Split (%)*] [money] NULL,     [F12] [nvarchar](255) NULL,     [Supplier_Amount_(incl GST)] [money] NULL,     [Supplier_Amount_(GST)] [money] NULL,     [Adviser Share (ex GST)] [money] NULL,     [F16] [nvarchar](255) NULL,     [F17] [nvarchar](255) NULL,     [Adviser Share (GST)] [money] NULL,     [Adviser Share (incl GST)] [money] NULL,     [LastNameOnly] [nvarchar](255) NULL,     [FirstNameOnly] [nvarchar](255) NULL,     [FirstFourLastName] [nvarchar](255) NULL,     [ClientCode] [nvarchar](255) NULL,     [IndexMatch] [nvarchar](255) NULL,     [F25] [nvarchar](255) NULL,     [F26] [float] NULL ) ON [PRIMARY] GO 

FMAY

Table FMAY contains the following columns:

  • commission amounts
  • a Lender Reference Number
  • client names (LastName, FirstName).

This report comes in monthly and contains monthly commissions for some of the clients (usually contains 350-400 records).

CREATE TABLE [Finance_Commissions_May26_2021$  ] (     CommissionAmount,     LenderReferenceNumber,     ClientLastName,     ClientFirstName ) 

& here is the code for table CCC:

USE [FinanceCommissions26May2021] GO  /****** Object:  Table [dbo].[Consolidated_ClientCodes$  ]    Script Date: 22/06/2021 11:07:27 AM ******/ SET ANSI_NULLS ON GO  SET QUOTED_IDENTIFIER ON GO  CREATE TABLE [dbo].[Consolidated_ClientCodes$  ](     [FirstFourLastName] [nvarchar](255) NULL,     [ClientCode] [nvarchar](255) NULL,     [Client] [nvarchar](255) NULL ) ON [PRIMARY] GO 

CCC

Table CCC contains the following columns:

  • client names
    • (different format though – i.e. FirstName,LastName)
  • a client code (which is a unique identifier (Primary key) assigned to each client.
    • Client code takes the form of SMIT123.
    • e.g. for "John, Smith" it’s SMIT123
    • for "Mark, Smith" it’s SMIT345, and so on.
    • i.e. First Four letters of last name followed by a unique 3 digit code.

This table contains Client names and unique Client codes for ALL clients (more than 7000 records)

CREATE TABLE ['20210617145928-Exception - No C$  '] (     ClientCode PRIMARY KEY, -- '\w\w\w\w\d\d\d'     FirstName,     LastName ) 

The problem

My aim is : To assign this unique Client code to each client within the FMAY table.

Since there was NO common column between tables FMAY and CCC, I created a new column (FirstFourLastName) which parses out FirstFour letters of Last Name (like "SMIT") from both Tables.

Then using this new column (FirstFourLastName), I wrote code for Inner Join in SQL with an aim to assign the Unique "Client Code" to each record in table FMAY.

I am able to get the correct client code assigned but not able to ONLY select distinct records.

My code is:

select     FMAY.[Lender Reference],     CCC.Client,     CCC.ClientCode,     FMAY.FirstFourLastName,     FMAY.[Adviser Share (ex GST)],     FMAY.[Adviser Share (GST)],     FMAY.[Adviser Share (incl GST)],     FMAY.Product  from     [FinanceCommissions26May2021].[dbo].[Finance_Commissions_May26_2021$  ] FMAY     inner join [FinanceCommissions26May2021].[dbo].['20210617145928-Exception - No C$  '] CCC on         FMAY.FirstFourLastName = CCC.FirstFourLastName 

The above code is giving me 6300 records, with the correct client code attached to each record though! However, I only need to assign Client Code to the 350-400 records in the FMAY table.

How can I select distinct records for my problem please?

How to select and delete all Output cells within current Selection only?

A similar question was already here: How to select and delete all Output cells?. But I have a more specific one: I want to delete all output cells within the currently selected cells only.

For this purpose I want to add an additional menu item Cell>Delete All Output within Selection.

Probably one would have to add something to

/usr/local/Wolfram/Mathematica/12.1/SystemFiles/FrontEnd/TextResources/X/MenuSetup.tr

Has anybody already made such an improvement and could he please post it here?

(The version in this case is 12.1 and it probably has to reflect the version in question. This path is for a Linux machine. On a Windows machine the paths begin at a different root but parts from Wolfram to TextRessources probably will look the same, except for the forward slashes being replaced by backward ones- /X/ might be special for Linux).

MySQL – Select Records that matches all values

If a table "XYZ" contains two columns as combine primary key, I need to select records that contain both Value1 and Value2 in COLUMN_B ( Result: ID4 )

I tried with "IN" query but it returns the records which contain VALUE1 OR VALUE2 in COLUMN_B (ID1, ID2, ID3, ID4).

Can anybody please help me to write a MySQL query that can do this.

COLUMN_A    COLUMN_B  ID1         Value1 ID2         Value1 ID3         Value2 ID4         Value1 ID4         Value2 ID5         Value3 ID3         Value3 

Thanks in Advance.

Non-deterministic performance of query on select, from 1s to 60s on table with 1 billion rows

I’m trying to investigate why the performance of this query is so non-deterministic. It can take anywhere from 1 seconds, to 60 seconds and above. The nature of the query is to select a "time window", and get all rows from within that time window.

Here’s the query in question, running on a table of approximately 1 billion rows:

SELECT CAST(extract(EPOCH from ts)*1000000 as bigint) as ts     , ticks     , quantity     , side FROM order_book WHERE ts >= TO_TIMESTAMP(1618882633073383/1000000.0)     AND ts < TO_TIMESTAMP(1618969033073383/1000000.0)     AND zx_prod_id = 0 ORDER BY ts ASC, del desc 

The values within TO_TIMESTAMP will keep sliding forward as I walk the whole table. Here is the EXPLAIN ANALYZE output for the same query on two different time windows:

Slow Performance

Gather Merge  (cost=105996.20..177498.48 rows=586308 width=18) (actual time=45196.559..45280.769 rows=539265 loops=1)   Workers Planned: 6   Workers Launched: 6   Buffers: shared hit=116386 read=42298   ->  Sort  (cost=104996.11..105240.40 rows=97718 width=18) (actual time=45169.717..45176.775 rows=77038 loops=7)         Sort Key: (((date_part('epoch'::text, _hyper_16_214_chunk.ts) * '1000000'::double precision))::bigint), _hyper_16_214_chunk.del DESC         Sort Method: quicksort  Memory: 9327kB         Worker 0:  Sort Method: quicksort  Memory: 8967kB         Worker 1:  Sort Method: quicksort  Memory: 9121kB         Worker 2:  Sort Method: quicksort  Memory: 9098kB         Worker 3:  Sort Method: quicksort  Memory: 9075kB         Worker 4:  Sort Method: quicksort  Memory: 9019kB         Worker 5:  Sort Method: quicksort  Memory: 9031kB         Buffers: shared hit=116386 read=42298         ->  Result  (cost=0.57..96897.07 rows=97718 width=18) (actual time=7.475..45131.932 rows=77038 loops=7)               Buffers: shared hit=116296 read=42298               ->  Parallel Index Scan using _hyper_16_214_chunk_order_book_ts_idx on _hyper_16_214_chunk  (cost=0.57..95187.01 rows=97718 width=18) (actual time=7.455..45101.670 rows=77038 loops=7)                     Index Cond: ((ts >= '2021-04-22 01:34:31.357179+00'::timestamp with time zone) AND (ts < '2021-04-22 02:34:31.357179+00'::timestamp with time zone))                     Filter: (zx_prod_id = 0)                     Rows Removed by Filter: 465513                     Buffers: shared hit=116296 read=42298 Planning Time: 1.107 ms JIT:   Functions: 49   Options: Inlining false, Optimization false, Expressions true, Deforming true   Timing: Generation 9.273 ms, Inlining 0.000 ms, Optimization 2.008 ms, Emission 36.235 ms, Total 47.517 ms Execution Time: 45335.178 ms 

Fast Performance

Gather Merge  (cost=105095.94..170457.62 rows=535956 width=18) (actual time=172.723..240.628 rows=546367 loops=1)   Workers Planned: 6   Workers Launched: 6   Buffers: shared hit=158212   ->  Sort  (cost=104095.84..104319.16 rows=89326 width=18) (actual time=146.702..152.849 rows=78052 loops=7)         Sort Key: (((date_part('epoch'::text, _hyper_16_214_chunk.ts) * '1000000'::double precision))::bigint), _hyper_16_214_chunk.del DESC         Sort Method: quicksort  Memory: 11366kB         Worker 0:  Sort Method: quicksort  Memory: 8664kB         Worker 1:  Sort Method: quicksort  Memory: 8986kB         Worker 2:  Sort Method: quicksort  Memory: 9116kB         Worker 3:  Sort Method: quicksort  Memory: 8858kB         Worker 4:  Sort Method: quicksort  Memory: 9057kB         Worker 5:  Sort Method: quicksort  Memory: 6611kB         Buffers: shared hit=158212         ->  Result  (cost=0.57..96750.21 rows=89326 width=18) (actual time=6.145..127.591 rows=78052 loops=7)               Buffers: shared hit=158122               ->  Parallel Index Scan using _hyper_16_214_chunk_order_book_ts_idx on _hyper_16_214_chunk  (cost=0.57..95187.01 rows=89326 width=18) (actual time=6.124..114.023 rows=78052 loops=7)                     Index Cond: ((ts >= '2021-04-22 01:34:31.357179+00'::timestamp with time zone) AND (ts < '2021-04-22 02:34:31.357179+00'::timestamp with time zone))                     Filter: (zx_prod_id = 4)                     Rows Removed by Filter: 464498                     Buffers: shared hit=158122 Planning Time: 0.419 ms JIT:   Functions: 49   Options: Inlining false, Optimization false, Expressions true, Deforming true   Timing: Generation 10.405 ms, Inlining 0.000 ms, Optimization 2.185 ms, Emission 39.188 ms, Total 51.778 ms Execution Time: 274.413 ms 

I interpreted this output as most of the blame lying on this parallel index scan.

At first, I tried to raise work_mem to 1 GB and shared_buffers to 24 GB, thinking that maybe it couldn’t fit all the stuff it needed in RAM, but that didn’t seem to help.

Next, I tried creating an index on (zx_prod_id, ts), thinking that the filter on the parallel index scan might be taking a while, but that didn’t seem to do anything either.

I’m no database expert, and so I’ve kind of exhausted the limits of my knowledge.

Thanks in advance for any suggestions!

How can I create a temp table from a SELECT in Azure Synapse

I have the following statement that works when I omit the ORDER BY Clause or if I run it as a select and omit the create table part but I need both to ensure my generated key is sequenced correctly

Any ideas?

Msg 104381, Level 16, State 1, Line 18 The ORDER BY clause is invalid in views, CREATE TABLE AS SELECT, INSERT SELECT, SELECT INTO, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

CREATE TABLE #demo WITH (DISTRIBUTION = ROUND_ROBIN) AS SELECT         ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS ID,        schemas.name as [schema],         tables.name as [table],         columns.column_id as [ordinal],        columns.name as [column],        types.name as [type] FROM SYS.COLUMNS   inner join sys.types           on  types.system_type_id = columns.system_type_id   inner join sys.tables           on  tables.object_id = columns.object_id   inner join sys.schemas           on  schemas.schema_id = tables.schema_id order by schemas.name,         tables.name,         columns.column_id  

Select list of attributes that exist in all groups

select distinct(p) from Pub; /* phdthesis article proceedings incollection inproceedings www book mastersthesis */ select * from field limit 20; /* tr/meltdown/s18,0,author tr/meltdown/s18,1,author tr/meltdown/s18,2,author tr/meltdown/s18,3,author tr/meltdown/s18,4,author tr/meltdown/s18,5,author tr/meltdown/s18,6,author tr/meltdown/s18,7,author tr/meltdown/s18,8,author tr/meltdown/s18,9,author tr/meltdown/s18,10,title tr/meltdown/s18,11,journal tr/meltdown/s18,12,year tr/meltdown/s18,13,ee tr/meltdown/m18,0,author tr/meltdown/m18,1,author tr/meltdown/m18,2,author tr/meltdown/m18,3,author tr/meltdown/m18,4,author tr/meltdown/m18,5,author */ 

pub and field join on column k (primary key)

I want to write a query that returns only those attributes (author/journal/year etc.) that have at least one record in every publication type: phdthesis, book, etc.

Select maximum of a count in a grouped clause

I have the following tables:

Vehicles(v͟i͟n͟, model,category) Sales(s͟a͟l͟e͟I͟D͟, staffID,customerID,date) vehicleSold(saleID,v͟i͟n͟,salePrice) 

When I join these tables using:

select YEAR(Sales.saleDate)      , Vehicles.model      , count(Vehicles.model) 'Sold'      , Vehicles.category   from Vehicles    JOIN vehicleSold     on Vehicles.vin = vehicleSold.vin   JOIN Sales      on Sales.saleID = vehicleSold.saleID  group      by YEAR(Sales.saleDate)      , Vehicles.model      , Vehicles.category; 

Result is:

+----------------------+-------------+------+----------------+ | YEAR(Sales.saleDate) | model       | Sold | category       | +----------------------+-------------+------+----------------+ |                 2020 | Altima      |    1 | car            | |                 2020 | Flying Spur |    2 | car            | |                 2020 | Lifan E3    |    3 | Electric Moped | |                 2020 | Ridgeline   |    2 | truck          | |                 2020 | Shiver      |    4 | motorbike      | +----------------------+-------------+------+----------------+ 

Out of this table I want to get the model that was most sold in a category. So, in this case I only want to return a 2020, Flying Spur, car as the only row in category car because it was the most sold in 2020 in its category. I tried using a subquery is MAX(COUNT(*)) but I guess that is not supported in mysql. If anyone could point out my mistake and has any idea how to do this then that would be big help!

Coloured integers: Select outcome unexpected (part of a graph based problem using coloured vertex labels)

I use the following code output and need to retrieve the position of the part containing the blue-coloured integer 4

Part 1:

List[Placed[List[       Style[3, Rule[LineColor, RGBColor[1, 0, 0]],         Rule[FrontFaceColor, RGBColor[1, 0, 0]],         Rule[BackFaceColor, RGBColor[1, 0, 0]],         Rule[GraphicsColor, RGBColor[1, 0, 0]],         Rule[FontColor, RGBColor[1, 0, 0]]],        Style[4, Rule[LineColor, RGBColor[0, 0, 1]],         Rule[FrontFaceColor, RGBColor[0, 0, 1]],         Rule[BackFaceColor, RGBColor[0, 0, 1]],         Rule[GraphicsColor, RGBColor[0, 0, 1]],         Rule[FontColor, RGBColor[0, 0, 1]]]], List[Before, After]], Placed[      List[Style[4, Rule[LineColor, RGBColor[1, 0, 0]],         Rule[FrontFaceColor, RGBColor[1, 0, 0]],         Rule[BackFaceColor, RGBColor[1, 0, 0]],         Rule[GraphicsColor, RGBColor[1, 0, 0]],         Rule[FontColor, RGBColor[1, 0, 0]]],        Style[3, Rule[LineColor, RGBColor[0, 0, 1]],         Rule[FrontFaceColor, RGBColor[0, 0, 1]],         Rule[BackFaceColor, RGBColor[0, 0, 1]],         Rule[GraphicsColor, RGBColor[0, 0, 1]],         Rule[FontColor, RGBColor[0, 0, 1]]]], List[Before, After]]] 

I tried:

Part 2:

        Select[List[Placed[List[               Style[3, Rule[LineColor, RGBColor[1, 0, 0]],                 Rule[FrontFaceColor, RGBColor[1, 0, 0]],                 Rule[BackFaceColor, RGBColor[1, 0, 0]],                 Rule[GraphicsColor, RGBColor[1, 0, 0]],                 Rule[FontColor, RGBColor[1, 0, 0]]],                Style[4, Rule[LineColor, RGBColor[0, 0, 1]],                 Rule[FrontFaceColor, RGBColor[0, 0, 1]],                 Rule[BackFaceColor, RGBColor[0, 0, 1]],                 Rule[GraphicsColor, RGBColor[0, 0, 1]],                 Rule[FontColor, RGBColor[0, 0, 1]]]], List[Before, After]], Placed[              List[Style[4, Rule[LineColor, RGBColor[1, 0, 0]],                 Rule[FrontFaceColor, RGBColor[1, 0, 0]],                 Rule[BackFaceColor, RGBColor[1, 0, 0]],                 Rule[GraphicsColor, RGBColor[1, 0, 0]],                 Rule[FontColor, RGBColor[1, 0, 0]]],                Style[3, Rule[LineColor, RGBColor[0, 0, 1]],                 Rule[FrontFaceColor, RGBColor[0, 0, 1]],                 Rule[BackFaceColor, RGBColor[0, 0, 1]],                 Rule[GraphicsColor, RGBColor[0, 0, 1]],                 Rule[FontColor, RGBColor[0, 0, 1]]]], List[Before, After]]], Select[#[[1]][[2]], IntegerQ] &  == 4] 

Which produces { }

However

Part 3

 a = Placed[     List[Style[3, Rule[LineColor, RGBColor[1, 0, 0]],        Rule[FrontFaceColor, RGBColor[1, 0, 0]],        Rule[BackFaceColor, RGBColor[1, 0, 0]],        Rule[GraphicsColor, RGBColor[1, 0, 0]],        Rule[FontColor, RGBColor[1, 0, 0]]],       Style[4, Rule[LineColor, RGBColor[0, 0, 1]],        Rule[FrontFaceColor, RGBColor[0, 0, 1]],        Rule[BackFaceColor, RGBColor[0, 0, 1]],        Rule[GraphicsColor, RGBColor[0, 0, 1]],        Rule[FontColor, RGBColor[0, 0, 1]]]],      List[Before, After]][[1]][[2]]  Select[a,IntegerQ] 

produces 4 as expected.

Hence I would have expected Part 2 to produce the expression containing the blue label 4, i.e. the following result

Placed[List[           Style[3, Rule[LineColor, RGBColor[1, 0, 0]],             Rule[FrontFaceColor, RGBColor[1, 0, 0]],             Rule[BackFaceColor, RGBColor[1, 0, 0]],             Rule[GraphicsColor, RGBColor[1, 0, 0]],             Rule[FontColor, RGBColor[1, 0, 0]]],            Style[4, Rule[LineColor, RGBColor[0, 0, 1]],             Rule[FrontFaceColor, RGBColor[0, 0, 1]],             Rule[BackFaceColor, RGBColor[0, 0, 1]],             Rule[GraphicsColor, RGBColor[0, 0, 1]],             Rule[FontColor, RGBColor[0, 0, 1]]]], List[Before, After]] 

On closer inspection I tried the following:

FullForm[Select[a, IntegerQ]] 

which results in

Style[4] 

However, Style[4] should not be the result of a selection for integers.

What is going on?